SQL Query Help

SQL 321 Reputation points
2023-04-20T00:42:31.18+00:00

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) expectedoutput

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 23,796 Reputation points Microsoft Vendor
    2023-04-20T01:48:46.18+00:00

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful