Skip to content
Advertisement

Sum last two records including last record of a group

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.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement