Android Question How to use sum() in SQL?

Theera · Mar 16, 2023

I have used the commands to keep Csr.Getstring("Guest") as variable.

B4X:

Csr = SQL1.ExecQuery("SELECT ID,Guest FROM RentTable")
Csr.Position = row

and then I used other command

B4X:

Dim TotalCostPaid As String=SQL1.ExecQuerySingleResult("SELECT sum(CostPaid) FROM RentTable WHERE Guest=Csr.Getstring("Guest")")

Why it has error? How do I code? I would like to get TotalCostPaid from the same guest who rents many rooms.

aeric · Mar 16, 2023

Your second SQL command is wrong. You need to use parameterized query or string literal.

Theera · Mar 16, 2023

aeric said:
Your second SQL command is wrong. You need to use parameterized query or string literal.

Could you show me the code? Idon't understand English. I'm still learning English and programming together in the same time.

aeric · Mar 16, 2023

Parameterized Query

B4X:

Dim strGuest As String = csr.GetString("Guest")
Dim strSQL As String = "SELECT SUM(CostPaid) FROM RentTable WHERE Guest = ?"
Dim TotalCostPaid As Double = SQL1.ExecQuerySingleResult2(strSQL, Array As String(strGuest))
Log(NumberFormat2(TotalCostPaid, 1, 2, 2, True))

String Literal

B4X:

Dim strGuest As String = csr.GetString("Guest")
Dim strSQL As String = $"SELECT SUM(CostPaid) FROM RentTable WHERE Guest = '${strGuest}'"$
Dim TotalCostPaid As Double = SQL1.ExecQuerySingleResult(strSQL)
Log(NumberFormat2(TotalCostPaid, 1, 2, 2, True))

Theera · Mar 16, 2023

Thank you.I will try coding thefollows.

corwin42 · Mar 16, 2023

Is ID the primary key of the RentTable or is it the ID of the guest?

If it ist the ID-number of a guest or you don't really need to select it if it is the primary key you can do this in just one SQL Statement:

SQL:

SELECT ID, Guest, sum(CostPaid) FROM RentTable GROUP BY ID, Guest

(If ID is the ID-number of a guest)

SQL:

SELECT Guest, sum(CostPaid) FROM RentTable GROUP BY Guest

(If ID is the primary key of RentTable)

Theera · Mar 16, 2023

corwin42 said:
Is ID the primary key of the RentTable or is it the ID of the guest?

If it ist the ID-number of a guest or you don't really need to select it if it is the primary key you can do this in just one SQL Statement:

SQL:

SELECT ID, Guest, sum(CostPaid) FROM RentTable GROUP BY ID, Guest

(If ID is the ID-number of a guest)

SQL:

SELECT Guest, sum(CostPaid) FROM RentTable GROUP BY Guest

(If ID is the primary key of RentTable)

Id is primary key.Thank you, sir.

Theera · Mar 16, 2023

corwin42 said:
Is ID the primary key of the RentTable or is it the ID of the guest?

If it ist the ID-number of a guest or you don't really need to select it if it is the primary key you can do this in just one SQL Statement:

SQL:

SELECT ID, Guest, sum(CostPaid) FROM RentTable GROUP BY ID, Guest

(If ID is the ID-number of a guest)

SQL:

SELECT Guest, sum(CostPaid) FROM RentTable GROUP BY Guest

(If ID is the primary key of RentTable)

Id is primary key.Thank you, sir.I don't know that what is the primarykey be useful.

aeric · Mar 17, 2023

Theera said:
I don't know that what is the primarykey be useful

Primary key is use to identify the record that is unique, meaning one guest has id = 1, another guest has id = 2.
If you want to develop database driven app, you must learn and have knowledge of SQL.
You need to understand the concept of Primary key, Foreign key, One-to-One relationship, One-to-Many, Many-to-Many relationship.

Mahares · Mar 17, 2023

Theera said:
Id is primary key.Thank you, sir.I don't know that what is the primarykey be useful.

aeric said:
Primary key is use to identify the record that is unique,

I can see why @Theera would get confused with the answer by @corwin42 , because the answer has nothing to do with PRIMARY KEY. If he wants to use the second solution given by corwin42, the code is still missing the WHERE clause to single out a given guest. Otherwise, it will return the total cost for each guest in the table grouped by guest. Your solution in post #4 is the correct one in this case.

corwin42 · Mar 17, 2023

The code in the first post starts a cursor over all guests and then in a second select it gets the sum(CostPaid) from the guest which is the current one in the cursor of the first select. I guess there is a loop in the code which walks over all guests. Otherwise it will not make much sense.

So I guess Theera wants to walk over all guests and get the sum() for all guests in the end. My select does this in only one statement, the other solution does this in 1+NumberOfGuests select statements. What do you think is more effective? I just want to make clear that the code in the first post can be optimized for speed and could be made simpler (you don't have to use parameters for the sql)

Alex_197 · Mar 18, 2023

You can get the same rsult with one statement

B4X:

SELECT Sum(CostPaid) as TotalPaid, Guest
FROM RentTable
Group By Guest

In this case you will get a total paid sum for each guest

chams · Mar 18, 2023

You can also order your results using the variable 'TotalPaid' : The 2 added last lines return the Top 10 Customers

SQL:

SELECT Sum(CostPaid) as TotalPaid, Guest
FROM RentTable
Group By Guest
ORDER BY TotalPaid DESC
LIMIT 10

Android Question How to use sum() in SQL?

Theera

Well-Known Member

aeric

Expert

Theera

Well-Known Member

aeric

Expert

Theera

Well-Known Member

corwin42

Expert

Theera

Well-Known Member

Theera

Well-Known Member

aeric

Expert

Mahares

Expert

corwin42

Expert

Alex_197

Well-Known Member

chams

Member

Similar Threads

Android Question How to use sum() in SQL?

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Expert

Expert

Expert

Well-Known Member

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency