This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and require output data to find the count of aggregate data or value (‘a’) from multiple columns.
Input Data:
|
1 2 3 4 5 6 7 |
ID tag Val1 Val2 ----------- ---- ---- ---- 1 A a b 2 A b r 3 M d c 4 A a n 5 M c a |
Expected Output:
|
1 2 3 4 |
tag ValCount ---- ----------- A 2 M 1 |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE GroupColumns ( ID INT ,tag CHAR(1) ,Val1 CHAR(1) ,Val2 CHAR(1) ) GO INSERT INTO GroupColumns VALUES (1,'A','a','b') ,(2,'A','b','r') ,(3,'M','d','c') ,(4,'A','a','n') ,(5,'M','c','a') GO |
Solution 1: Using CTE
|
1 2 3 4 5 6 7 8 |
with cte as ( select id, val1,tag from GroupColumns where val1 = 'a' union select id,val1,tag from GroupColumns where val2 = 'a' ) select tag, count(tag) as ValCount from cte group by tag |
Solution 2: Using SUM() Group BY
|
1 2 3 4 5 6 7 |
SELECT tag ,SUM(CASE val1 WHEN 'a' THEN 1 ELSE 0 END) +SUM(CASE val2 WHEN 'a' THEN 1 ELSE 0 END) ValCount FROM GroupColumns GROUP BY tag |
Please try the different solutions for this puzzle and share it via comment...
