SQL Puzzle | The Count Puzzle

In this puzzle you have to check for each Id we have to count a, b, c and d values. If for each Id these values are not present then do not include them. For more details please check the sample input and expected output.

Sample Input

Id Vals
1 a
1 b
1 c
1 d
2 a
2 d
3 d
4 g

Expected Output

Id cnts
1 4
2 2
3 1
4 0

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE Table1 ( Id VARCHAR(10) ,Vals VARCHAR(10) ) GO INSERT INTO Table1 VALUES (1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'), (2, 'a'), (2, 'd'), (3, 'd') GO INSERT INTO Table1 VALUES (4,'g') GO SELECT * FROM Table1 GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

SOLUTION 0 | USING SELECT & CASE

 ```-- select Id,ISNULL(SUM(IIF(Vals IN ('a','b','c','d'),1,0)),0) CountVals FROM Table1 group by Id -- ```

Output-1

 ```-- Id CountVals ---------- ----------- 1 4 2 2 3 1 4 0 (4 rows affected) -- ```

SOLUTION 1 | USING Outer Apply AND LEFT JOIN

 ```-- SELECT n.Id, COUNT(t1.Vals) CountVals FROM ( SELECT * FROM (SELECT Id FROM Table1 GROUP BY Id) t1 OUTER APPLY ( VALUES ('a'),('b'),('c'),('d')) AS y(t) )n LEFT JOIN Table1 t1 ON t1.Vals = n.t AND t1.Id = n.Id GROUP BY n.Id GO -- ```

Output-1

 ```-- Id CountVals ---------- ----------- 1 4 2 2 3 1 4 0 (4 rows affected) -- ```

SOLUTION 2 | USING PIVOT AND CASE

 ```-- ;WITH CTE AS ( SELECT * , 'Yes' Ya FROM Table1 ) ,CTE1 AS ( SELECT Id, [a],[b],[c],[d] FROM CTE PIVOT ( MAX(Ya) FOR Vals IN ([a],[b],[c],[d]) )t ) SELECT Id , CASE WHEN [a] = 'Yes' THEN 1 ELSE 0 END + CASE WHEN [b] = 'Yes' THEN 1 ELSE 0 END + CASE WHEN [c] = 'Yes' THEN 1 ELSE 0 END + CASE WHEN [d] = 'Yes' THEN 1 ELSE 0 END Cnt FROM CTE1 -- ```

Output-2

 ```-- Id CountVals ---------- ----------- 1 4 2 2 3 1 4 0 (4 rows affected) -- ```

SOLUTION 3 | USING MAX , CASE AND Group BY

 ```-- SELECT Id , CASE WHEN [a] = 'Yes' THEN 1 ELSE 0 END + CASE WHEN [b] = 'Yes' THEN 1 ELSE 0 END + CASE WHEN [c] = 'Yes' THEN 1 ELSE 0 END + CASE WHEN [d] = 'Yes' THEN 1 ELSE 0 END Cnt FROM ( SELECT Id ,MAX(CASE WHEN Vals = 'a' THEN 'Yes' ELSE 'No' END) [a] ,MAX(CASE WHEN Vals = 'b' THEN 'Yes' ELSE 'No' END) [b] ,MAX(CASE WHEN Vals = 'c' THEN 'Yes' ELSE 'No' END) [c] ,MAX(CASE WHEN Vals = 'd' THEN 'Yes' ELSE 'No' END) [d] FROM Table1 GROUP BY Id )k -- ```

Output-3

 ```-- Id CountVals ---------- ----------- 1 4 2 2 3 1 4 0 (4 rows affected) -- ```

SOLUTION 4 |

 ```-- SELECT t.Id,SUM(CASE WHEN CountVals IS NULL THEN 0 ELSE 1 END) cnts from Table1 t LEFT JOIN ( SELECT n.Id, COUNT(n.Vals) CountVals FROM Table1 n WHERE vals IN ('a','b','c','d') GROUP BY n.Id )k on t.Id = k.Id GROUP BY t.Id GO -- ```

Output-4

 ```-- Id CountVals ---------- ----------- 1 4 2 2 3 1 4 0 (4 rows affected) -- ```

SOLUTION 5 | Using CASE and SUM

 ```-- SELECT Id ,SUM(CASE WHEN Vals = 'a' THEN 1 ELSE 0 END) +SUM(CASE WHEN Vals = 'b' THEN 1 ELSE 0 END) +SUM(CASE WHEN Vals = 'c' THEN 1 ELSE 0 END) +SUM(CASE WHEN Vals = 'd' THEN 1 ELSE 0 END) Cnt FROM Table1 GROUP BY Id GO -- ```

Output-5

 ```-- Id CountVals ---------- ----------- 1 4 2 2 3 1 4 0 (4 rows affected) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com