March 17, 2020 at 4:05 pm
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)...
KeyID UserID Amount
1 12 1.00
2 09 1.00
3 12 5.00
4 12 6.00
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.
KeyID UserID Amount
1 12 1.00 <---exclude
2 09 1.00
3 12 5.00 <---exclude
4 12 6.00
5 10 2.00
Here is what I have....
SELECT SUM(Amount) as Total FROM Table
Total = 15 (Should be 9)
Thanks again.
March 17, 2020 at 4:27 pm
-- *** Consumable Test Data ***
CREATE TABLE #t
(
KeyID int NOT NULL
PRIMARY KEY
,UserID varchar(10) NOT NULL
,Amount money NOT NULL
);
INSERT INTO #t
VALUES (1, '12', 1.00)
,(2, '09', 1.00)
,(3, '12', 5.00)
,(4, '12', 6.00)
,(5, '10', 2.00);
--select * from #t;
-- *** End Consumable Test Data ***
WITH OrderedAmounts
AS
(
SELECT Amount
,ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY KeyId DESC) AS rn
FROM #t
)
SELECT SUM(Amount) AS Total
FROM OrderedAmounts
WHERE rn = 1;
March 17, 2020 at 5:12 pm
;with
ordered_amounts as (
select distinct first_value(amount) over (partition by userid order by keyid desc) amount from #t)
select
sum(amount) total
from
ordered_amounts;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 18, 2020 at 11:57 am
;with
ordered_amounts as (
select distinct first_value(amount) over (partition by userid order by keyid desc) amount from #t)
select
sum(amount) total
from
ordered_amounts;
This was not right. It should have included the PARTITION BY column(s) in the select list too.
;with
ordered_amounts as (
select distinct userid, first_value(amount) over (partition by userid order by keyid desc) amount from #t)
select
sum(amount) total
from
ordered_amounts;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply