Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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