472,983 Members | 2,404 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,983 software developers and data experts.

Sum question with excluding condition

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
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
Here is what I have....

SELECT SUM(Amount) as Total FROM Table

Total = 15 (Should be 9)

Thanks again.
Mar 17 '20 #1
5 5118
12,516 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
32,548 Expert Mod 16PB
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
9,065 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

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

Similar topics

by: J.P | last post by:
Hi! Anyone knows if it's possible to do an update if, and only if my condition is TRUE. Example: In MYTABLE I have three columns, like this: ID(INT), PUBLISH(ENUM(Y,N)),...
by: xixi | last post by:
hi, we are using db2 udb v8.1 on windows, i have a table contains over one million records, it has seperate own tablespace than others, with bufferpool size 250, i have created multiple views on...
by: Alpha | last post by:
C# doesn't allow multiple condition in a single if ? Or did I make a mistake with my syntax? It keep giving me syntax error when I put "and" or "or" in the if statemnt for multiple condition...
by: RA | last post by:
Is it possible to add an attribute for onClick event with an if condition. For example. If button is enabled then popup a message "Button Enabled" and if it is disabled popup message "Button...
by: Michael R | last post by:
Hello all. I'm currently writting a query that would be able to filter a table by a form's control value. If the value is null, the query should select all the rows of the table. The condition...
by: damezumari | last post by:
How do I get the total without a condition and the total with the condition at the same time? This is my mysql table: id datetimeconfirmed customerid amount 1 ...
by: fran7 | last post by:
Hi, Would anyone know how to populate an asp dropdown with a dependant condition. I need this dropdown to select only artists, ie from the authortwo field where in another field, ie extrathree, ...
by: sudeep kumar | last post by:
i want to validate many textboxes with same condition....Do i need to write code for each text boxes...plzz help
by: yuentong | last post by:
hi guy, My question is to create a poll system for basically 2 condition which is single vote and mulitiple vote per poll. For the single vote, it can be single vote per day or per duration. ...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.