CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
LearnByReading
LearnByReading

Reputation: 1883

Combining data in several rows into one row

I am having difficulty combining (merging) rows in SQL: I am looking for a row that contains

CLIENTID, TOTAL COUNTS AT ALL LEVELS, LEVEL COUNTs (at each level).

Here is an example: 

ClientID    Total Counts    Level1  Level2  Level3
1234566     12              9        3      0

Here is the code I have, and I've twisted and turned it but it always comes up short: it gives me nulls at every level except for the one. But I want data all combined into one row. Thank you very much in advance 

            SELECT u.clientID AS CLIENTID_SHORT, 
            CASE WHEN LEVEL='1' THEN COUNT(*) END AS Level1,
            CASE WHEN LEVEL='2' THEN COUNT(*) END AS Level2,
            CASE WHEN LEVEL='3' THEN COUNT(*) END AS Level3,
            CASE WHEN LEVEL='4' THEN COUNT(*) END AS Level4,
            CASE WHEN LEVEL='5' THEN COUNT(*) END AS Level5
            FROM CLIENTPROFILE u
            GROUP BY u.ClientID, u.LEVEL
            ORDER BY CLIENTID_SHORT

Upvotes: 1

Views: 66

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Apply count on top of case statement 

SELECT u.clientID AS CLIENTID_SHORT,
       Count(*) AS TotalLevelCount,
       Count(CASE WHEN LEVEL = 1 THEN 1 END) AS Level1,
       Count(CASE WHEN LEVEL = 2 THEN 1 END) AS Level2,
       Count(CASE WHEN LEVEL = 3 THEN 1 END) AS Level3,
       Count(CASE WHEN LEVEL = 4 THEN 1 END) AS Level4,
       Count(CASE WHEN LEVEL = 5 THEN 1 END) AS Level5
FROM   CLIENTPROFILE u
WHERE  LEVEL >= 1
       AND Level <= 5
GROUP  BY u.ClientID
ORDER  BY CLIENTID_SHORT

Note : You don't have to use single quotes while checking against integer columns.

Upvotes: 4

Related Questions