Grouping Rows based on two columns and including an aggregated column

user_13830264 0
Viewed 51

Sorry hard to create a descriptive subject but here is the explanation of what I am trying to do:

This is the shape of data I have:

enter image description here

So for each group of EffectiveDates, if there are multiple rows with the same SecGlobalId and their SecType is only "CASH" , I want to combine the sum of their Cost and have ONE row only for those so for example for 24th I want to have one row for USD that the value of its Cost column is 23.45 + 10.00 = 33.45 and then another one for 25th that will be 140.23 + 45.41

Here is the script of an example I created and I tried to do grouping but that is not right. So I am not sure how else to solve it.

CREATE TABLE #Practice
(
    SecGlobalID INT, 
    Name NVARCHAR(50), 
    EffectiveDate DATE,  
    Cost DECIMAL(20,2), 
    SecType NVARCHAR(5)
);

INSERT INTO #Practice (SecGlobalID, Name, Cost, EffectiveDate, SecType)
VALUES 
 (1234, 'USD', 23.45,  '2021-07-24', 'CASH')
 ,(1234, 'USD', 10.00,  '2021-07-24', 'CASH')
 ,(789, 'Tesla', 564.72,  '2021-07-24', 'STOCK')
 ,(5223, 'Starbucks', 4534.12,  '2021-07-24', 'STOCK')
 ,(9987, 'GER', 50.00,  '2021-07-24', 'CASH')

 ,(1234, 'USD', 140.23,  '2021-07-25', 'CASH')
 ,(1234, 'USD', 45.41,  '2021-07-25', 'CASH')
 ,(9987, 'GER', 5.4,  '2021-07-25', 'CASH')

 ,(43342, 'Security1', 3670.14,  '2021-07-26', 'STOCK')
 ,(7777, 'Security2', 66.35,  '2021-07-26', 'CASH')
 
SELECT SecGlobalID, Name, EffectiveDate, SUM(Cost), SecType 
FROM #Practice
GROUP BY SecGlobalID, Name, EffectiveDate,  SecType

DROP TABLE #Practice

So here is the screenshot of the expected result:

enter image description here

window-functions case sum tsql sql-server
2 Answers

You can use SUM() window function in a CASE expression:

SELECT DISTINCT
       SecGlobalID, Name, 
       CASE 
         WHEN SecType <> 'CASH' THEN Cost
         ELSE SUM(CASE WHEN SecType = 'CASH' THEN Cost END) OVER (PARTITION BY SecGlobalID, EffectiveDate) 
       END Cost, 
       EffectiveDate, SecType
FROM Practice

See the demo.

user_104988280 
SELECT SecGlobalID, Name, EffectiveDate, SUM(CASE WHEN SecType= 'CASH' THEN Cost ELSE 0 END), SecType 
FROM #Practice
GROUP BY SecGlobalID, Name, EffectiveDate,  SecType
Order by EffectiveDate
user_9252220
Related