Total for the whole single SQL-CodePudding

Date order number amount

The 2020-03-30, 10001, 1000
The 2020-03-30, 10001, 2000
The 2020-03-30, 10001, 3000
The 2020-03-30, 10002, 2000

To effect in the last class of aggregate amount

Date order number amount combined the single

The 2020-03-30, 10001, 1000, 6000
The 2020-03-30 10001 2000 0
The 2020-03-30 10001 3000 0
The 2020-03-30, 10002, 2000, 2000

CodePudding user response:

 

IF OBJECT_ID (N 'TEMPDB for. DBO. # T') IS NOT NULL 
DROP TABLE # T 
GO 

The CREATE TABLE # T 
(DT DATE, 
ORDER_NO VARCHAR (10), 
The AMOUNT INT) 

INSERT INTO # T 
SELECT '2020-03-30', '10001', 1000 UNION ALL 
SELECT '2020-03-30', '10001', 2000 UNION ALL 
SELECT '2020-03-30', '10001', 3000 UNION ALL 
SELECT '2020-03-30', '10002', 2000, 

SELECT DT, ORDER_NO, AMOUNT, 
CASE the WHEN SEQ=1 THEN TOTAL ELSE 0 END AS TOTAL 
The FROM 
(SELECT *, SUM (AMOUNT) OVER (PARTITION BY ORDER_NO, DT) AS the TOTAL, ROW_NUMBER () OVER (PARTITION BY ORDER_NO, DT ORDER BY 1) (SELECT) AS SEQ 
The FROM # T) AS A