Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ Group By on Multiple Columns Puzzle ]  – In this puzzle we have to count a’s for different Typ’s in columns Value1, Value2 and Value3. E.g. in sample data there are 3 a’s for Typ I and 1 a for Typ o. Please check out the sample input and expected output for details. Sample Input

ID Typ Value1 Value2 Value3
1 I a b
2 O a d f
3 I d b
4 O g l
5 I z g a
6 I z g a

Expected Output

Typ Counts
I 3
O 1

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table
CREATE TABLE GroupbyMultipleColumns
(
ID INT
,Typ VARCHAR(1)
,Value1 VARCHAR(1)
,Value2 VARCHAR(1)
,Value3 VARCHAR(1)
)
GO

--Insert Data
INSERT INTO GroupbyMultipleColumns(ID,Typ,Value1,Value2,Value3)
VALUES
(1,'I','a','b',''),
(2,'O','a','d','f'),
(3,'I','d','b',''),
(4,'O','g','l',''),
(5,'I','z','g','a'),
(6,'I','z','g','a')

--Verify Data
SELECT ID,Typ,Value1,Value2,Value3 FROM GroupbyMultipleColumns

Update May 14 | Solutions



--


---------------------------------------
--Sol 1
---------------------------------------

SELECT 
       Typ
       ,SUM(CASE Value1 WHEN 'a' THEN 1 ELSE 0 END) 
       +SUM(CASE Value2 WHEN 'a' THEN 1 ELSE 0 END)
       +SUM(CASE Value3 WHEN 'a' THEN 1 ELSE 0 END)
       Counts 
FROM GroupbyMultipleColumns
GROUP BY Typ


--

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

Keep Learning

http://MSBISkills.com

Advertisements