combine distinct & sum in one sql statement?

[daveigh]

ok guys, i have this list of multiple user id's with their corresponding site points stored in one table. and this is what i have to do:
1) show a list of "distinct" user id's,
2) calculate sum of all the points those distinct USER IDs had
3) And list them ordered by that sum

Havent come across like this before, so this is the current one i was trying to execute:

sql = "SELECT DISTINCT userid, SUM(pointsrecvd) as totalpoints FROM tblPointSystem GROUP BY userid ORDER BY SUM(pointsrecvd) DESC;"

...but i still get multiple user ids... any ideas?


______________CRYOcoustic_____________

 

[onpnt]

Please post this in the Jet SQL forum @ forum701

This may also be of help
Development practices when using SQL/Interfaces faq333-4896

 

[DotNetGnat]

try this:

sql = "SELECT userid, SUM(pointsrecvd) as totalpoints FROM tblPointSystem GROUP BY userid ORDER BY 2 DESC;"

-DNG

 

[Bullschmidt]

Seems like subqueries would be helpful.

Here's a little something I wrote to myself awhile back about using subqueries:

Example of one query (QueryB) based on the results of another query (QueryA):

QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" & QueryA & ")"

But the following is even faster and allows for more than one field to be returned in QueryA:

QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

So QueryA would include all the CustID's for customers starting with A.

And QueryB would include more fields in the customers table (i.e. not just the CustID field) for the records returned in QueryA (which was the customers starting with A).


Best regards,
-Paul

http://www.Bullschmidt.com

- Freelance Web and Database Developer

http://www.Bullschmidt.com/DevTip.asp

- Classic ASP Design Tips

 

[daveigh]

tnx Paul. i'll try that out. the previous query strings generate duplicate userids. let u guys know.

______________CRYOcoustic_____________