SQL - Group to get sum, but also return string if sum is 0
I have the following table:
ID BuyOrSell Total
4 B 10
4 B 11
4 S 13
4 S 29
8 B 20
9 S 23
What I am trying to do is to have the sum of columns B and S for each id, and if there is no B or S for id, has a row with 0, so the expected result would be
ID BuyOrSell Total
4 B 21
4 S 42
8 B 20
8 S 0
9 S 23
9 B 0
I tried this and it's kind of what I'm doing, but not quite:
DECLARE @Temp Table (ID int, BuyOrSell VARCHAR(1), charge Decimal)
INSERT INTO @Temp
SELECT 4, 'B', 10 UNION ALL
SELECT 4, 'B', 11 UNION ALL
SELECT 4, 'S', 13 UNION ALL
SELECT 4, 'S', 29 UNION ALL
SELECT 8, 'B', 20 UNION ALL
SELECT 9, 'S', 23
;With Results AS
(
SELECT ID,
BuyOrSell,
SUM(charge) AS TOTAL
FROM @Temp
Group by ID, BuyOrSell
)
Select t.*,max(
case when BuyOrSell = 'B' then 'Bfound'
end) over (partition by ID) as ref
,max(
case when BuyOrSell = 'S' then 'Sfound'
end) over (partition by ID) as ref
FROM Results t;
thank
+3
source to share
3 answers
Try the following:
;WITH CTE(ID, BuyOrSell) AS(
SELECT
ID, T.BuyOrSell
FROM @Temp
CROSS JOIN(
SELECT 'B' UNION ALL SELECT 'S'
)T(BuyOrSell)
GROUP BY ID, T.BuyOrSell
)
SELECT
C.ID,
C.BuyOrSell,
Total = ISNULL(SUM(T.charge), 0)
FROM CTE C
LEFT JOIN @Temp T
ON T.ID = C.ID
AND T.BuyOrSell = C.BuyOrSell
GROUP BY C.ID, C.BuyOrSell
ORDER BY C.ID, C.BuyOrSell
+3
source to share
@ 03Usr, even though your question has been answered, try this:
SELECT two.ID,
two.BuyOrSell,
ISNULL (one.Total, 0) Total
FROM
(SELECT ID,
BuyOrSell,
SUM (Total) Total
FROM @Temp
GROUP BY ID, BuyOrSell) one
LEFT OUTER JOIN
(SELECT ID,
BuyOrSell
FROM @Temp
GROUP BY ID,
BuyOrSell) two
ON one.ID = two.ID
AND one.BuyOrSell = two.BuyOrSell;
0
source to share
Here's a solution with a tricky connection:
SELECT t1.ID,
v.l as BuyOrSell,
SUM(CASE WHEN t1.BuyOrSell = v.l THEN t1.charge ELSE 0 END) AS Total
FROM @Temp t1
JOIN (VALUES('B'),('S')) v(l)
ON t1.BuyOrSell = CASE WHEN EXISTS(SELECT * FROM @Temp t2
WHERE t2.ID = t1.ID AND t2.BuyOrSell <> t1.BuyOrSell)
THEN v.l ELSE t1.BuyOrSell END
GROUP BY t1.ID, v.l
ORDER BY t1.ID, v.l
Output:
ID l Total
4 B 21
4 S 42
8 B 20
8 S 0
9 B 0
9 S 23
0
source to share