By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,470 Members | 966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,470 IT Pros & Developers. It's quick & easy.

Sum question with excluding condition

CroCrew
Expert 100+
P: 564
Hello Everyone,

First I would like to thank you to anyone that give an answer to my question.

I have a table like this one below (KeyID is auto-increment)...
Expand|Select|Wrap|Line Numbers
  1. KeyID   UserID   Amount
  2. 1        12        1.00
  3. 2        09        1.00
  4. 3        12        5.00
  5. 4        12        6.00
  6. 5        10        2.00
  7.  
I can't figure out the syntax to query the table to sum all the "Amount"s and exclude older records for like UserIDs.
Expand|Select|Wrap|Line Numbers
  1. KeyID   UserID   Amount
  2. 1        12        1.00  <---exclude
  3. 2        09        1.00
  4. 3        12        5.00  <---exclude
  5. 4        12        6.00
  6. 5        10        2.00
  7.  
Here is what I have....

SELECT SUM(Amount) as Total FROM Table

Total = 15 (Should be 9)

Thanks again.
1 Week Ago #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,401
Use an aggregate query to select the max KeyID grouping by UserID. Join that aggregate query back to the table on that max KeyID and UserID to get the latest record for each user.
1 Week Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.