468,241 Members | 1,420 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,241 developers. It's quick & easy.

Sum question with excluding condition

CroCrew
564 Expert 512MB
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.
Mar 17 '20 #1
5 4074
Rabbit
12,511 Expert Mod 8TB
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.
Mar 17 '20 #2
NeoPa
32,053 Expert Mod 16PB
CroCrew.
Are you trying to get the sum of [Amount] for the latest records of all distinct users?
Apr 25 '20 #3
Check both query below:

Expand|Select|Wrap|Line Numbers
  1. select sum(t1.Amount) as Amount
  2. from tblTest as t1
  3. where t1.KeyID = (select max(t2.KeyID)
  4.                       from tblTest as t2
  5.                       where t2.UserID = t1.UserID
  6.                      );

Expand|Select|Wrap|Line Numbers
  1. ;with cte as (
  2.     select KeyID, UserID, Amount,
  3.            row_number() over(partition by UserID order by KeyID desc) as RowNum
  4.         from tblTest
  5. )
  6. select SUM(Amount) as Amount
  7.     from cte
  8.     where RowNum = 1
Jul 1 '20 #4
Thank you for your expantiation
Jul 1 '20 #5
Banfa
9,033 Expert Mod 8TB
Something to consider with this solution is that the KeyId field is not infinite; that is at some point, if you ever remove records and the key wraps round you will get the situation where the order of the KeyId does not correspond to the chronological order that the records were inserted.

OK this might be quite hypothetical but as software engineers it is our job consider all the problems that could arise with our solution and then decide if we need to mitigate against them. In this case possibly not for a simple exercise which will never have more than a few rows but a table that processes and then deletes millions of rows a day might have future trouble with this solution.

Additionally the mitigation in this case is quite easy as all you need to do is add a datatime column to you table to record the entry time for the record and use that for your ordering.

In general it is a bad idea to assume a pattern in a small test data set will (or won't) be present in a larger real world data set. Your only real guarantee with a key field is that it is unique (assuming the person creating the table did it properly).

It comes down to 4 questions
What is the potential issue?
What will be the consequences if this issue arises?
How often will or likely is it that this issue arises?
How easy is it to mitigate against?
Jul 2 '20 #6

Post your reply

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

Similar topics

8 posts views Thread by xixi | last post: by
9 posts views Thread by Alpha | last post: by
4 posts views Thread by RA | last post: by
1 post views Thread by damezumari | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.