SQL Query Help

Question

Hi: I have this SQL and need some help. The distinct and nested case statement is not working for and there could be better solution. DISTINCT Grp_Nbr, AUD

WHEN AUD = SUB then SUM of Total where Rln_CD = 1

WHEN AUD = SSB then SUM of Total where Rln_CD in (1,2)

WHEN AUD = MEM then SUM of Total where Rln_CD in (1,2,3,4,5,6.........)

DECLARE @TestTbl TABLE
(Grp_Nbr int,
 Rln_Cd int,
 AUD varchar(10),
 Total int)

INSERT INTO @TestTbl values (14175889, 1, 'SUB', 4)
INSERT INTO @TestTbl values (14175889, 2, 'SUB', 3)
INSERT INTO @TestTbl values (14175889, 3, 'SUB', 5)

INSERT INTO @TestTbl values (14175890, 1, 'SSB', 6)
INSERT INTO @TestTbl values (14175890, 2, 'SSB', 2)
INSERT INTO @TestTbl values (14175890, 3, 'SSB', 2)

INSERT INTO @TestTbl values (14175891, 1, 'MEM', 8)
INSERT INTO @TestTbl values (14175891, 2, 'MEM', 2)
INSERT INTO @TestTbl values (14175891, 3, 'MEM', 4)
INSERT INTO @TestTbl values (14175891, 4, 'MEM', 1)


SELECT DISTINCT Grp_Nbr, AUD,
	   (CASE WHEN AUD = 'SUB' THEN 
			    (CASE WHEN Rln_Cd = 1 THEN SUM(Total) END)
		     WHEN AUD = 'SSB' THEN 
				(CASE WHEN Rln_Cd in (1,2) THEN SUM (Total) END)
		     WHEN AUD = 'MEM' THEN SUM(Total)
		END) AS Total
FROM @TestTbl
Group by Grp_Nbr, AUD

EXPECTED OUTPUT (Grp_Nbr, AUD, Total)

Answer 1

Hi @SQL

Try this query:

SELECT Grp_Nbr
      ,AUD
      ,SUM(CASE WHEN (AUD = 'SUB' AND Rln_Cd = 1) OR (AUD = 'SSB' AND Rln_Cd IN(1,2)) OR (AUD = 'MEM' AND Rln_Cd IN(1,2,3,4,5))THEN Total END)AS Total
FROM @TestTbl
Group by Grp_Nbr, AUD
ORDER BY Grp_Nbr

Also, you could use UNION like this:

SELECT Grp_Nbr,AUD,SUM(Total)AS Total FROM @TestTbl
WHERE AUD = 'SUB' AND Rln_Cd = 1
Group by Grp_Nbr, AUD
UNION
SELECT Grp_Nbr,AUD,SUM(Total)AS Total FROM @TestTbl
WHERE AUD = 'SSB' AND Rln_Cd IN(1,2)
Group by Grp_Nbr, AUD
UNION
SELECT Grp_Nbr,AUD,SUM(Total)AS Total FROM @TestTbl
WHERE AUD = 'MEM' AND Rln_Cd IN(1,2,3,4,5)
Group by Grp_Nbr, AUD
ORDER BY Grp_Nbr

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.