Android Question How to use sum() in SQL?

[Olaan]

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

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

and then I used other command

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]

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

 

[Olaan]

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]

Parameterized Query

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

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))

 

[Olaan]

Thank you.I will try coding thefollows.

 

[corwin42]

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:

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

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

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

(If ID is the primary key of RentTable)

 

[Olaan]

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:

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

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

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

(If ID is the primary key of RentTable)

Id is primary key.Thank you, sir.

 

[Olaan]

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:

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

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

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]

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]

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

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]

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]

You can get the same rsult with one statement

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]

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

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