In SQL Server 2017, how do I sum the last two records and show the last record in a single query?
CREATE TABLE Billing ( Customer CHAR(12), Month INT, Amount INT ) GO INSERT INTO Billing VALUES ('AAAA', 3, 5) INSERT INTO Billing VALUES ('AAAA', 2, 0) INSERT INTO Billing VALUES ('AAAA', 1, 2) INSERT INTO Billing VALUES ('BBBB', 10, 0) INSERT INTO Billing VALUES ('BBBB', 12, 1) INSERT INTO Billing VALUES ('BBBB', 11, 0) INSERT INTO Billing VALUES ('BBBB', 13, 6)
Expected output:
Customer Total Last 2 Bills Last Bill ----------------------------------------- AAAA 5 5 BBBB 7 6
I tried using SUM
with LAST_VALUE
with ORDER BY
Advertisement
Answer
You can filter out rows by using the ROW_NUMBER()
window function, as in:
select customer, sum(amount) as total_last_2_bills, sum(case when rn = 1 then amount else 0 end) as last_bill from ( select *, row_number() over (partition by customer order by month desc) as rn from billing ) x where rn <= 2 group by customer
See SQL Fiddle.