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
Improving my SQL BI Skills 
DECLARE @GroupbyMultipleColumns TABLE
(
ID INT
,Typ VARCHAR(1)
,Value1 VARCHAR(1)
,Value2 VARCHAR(1)
,Value3 VARCHAR(1)
)
–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’)
SELECT s.Typ
,SUM(s.v)
FROM (
SELECT Typ
,COUNT(Value1) AS ‘v’
FROM @GroupbyMultipleColumns
WHERE Value1 = ‘a’
GROUP BY
Typ
UNION ALL
SELECT Typ
,COUNT(Value2) AS ‘v’
FROM @GroupbyMultipleColumns
WHERE Value2 = ‘a’
GROUP BY
Typ
UNION ALL
SELECT Typ
,COUNT(Value3) AS ‘v’
FROM @GroupbyMultipleColumns
WHERE Value3 = ‘a’
GROUP BY
Typ
) AS s
GROUP BY
s.Typ
LikeLike
; With Countsum as (
SELECT Typ, Orders
FROM
(SELECT ID,Typ,Value1,Value2,Value3
FROM GroupbyMultipleColumns) p
UNPIVOT
(Orders FOR Value IN
(Value1,Value2,Value3 )
)AS unpvt )
Select TYP, COUNT(*) as Counts from Countsum
WHERE Orders = ‘a’
Group by TYP
GO
LikeLike
SELECT Typ, COUNT(1)
FROM GroupbyMultipleColumns
unpivot
(
Val FOR col IN (Value1, Value2, Value3)
) as u
WHERE Val = ‘a’
GROUP BY Typ;
LikeLike
select Typ, sum(
case
when Value1=’a’ then 1
when Value2=’a’ then 1
when Value3=’a’ then 1
end
)
from GroupbyMultipleColumns
group by Typ
LikeLiked by 1 person
;with cte as
(
select Typ,count(Typ) cnt,value1,value2,value3 from GroupbyMultipleColumns group by Typ,
value1,value2,value3 having
Value1=’z’ or value2=’z’ or value3=’z’
)
select Typ,sum(cnt)as NoOfa from cte group by Typ
LikeLike
;with cte as
(
select Typ,count(Typ) cnt,value1,value2,value3 from GroupbyMultipleColumns group by Typ,
value1,value2,value3 having
Value1=’a’ or value2=’a’ or value3=’a’
)
select Typ,sum(cnt)as NoOfa from cte group by Typ
LikeLike
with cte as
(
select typ, len(value1+value2+value3)- len(replace (value1+value2+value3,’a’,”)) as req from GroupbyMultipleColumns
)
select distinct typ, sum(req) over (partition by typ order by typ) as counts from cte
LikeLiked by 1 person
Select Typ,count(Typ) as counts from GroupbyMultipleColumns
where value1 = ‘a’ or value2 = ‘a’ or value3 = ‘a’
group by Typ
LikeLiked by 1 person