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 | [ Fruit Count Puzzle ] – In this puzzle we have to count fruits per person. Please check out the sample input and expected output for details.
Sample Input
| Name | Fruit |
| Neeraj | MANGO |
| Neeraj | MANGO |
| Neeraj | MANGO |
| Neeraj | APPLE |
| Neeraj | ORANGE |
| Neeraj | LICHI |
| Neeraj | LICHI |
| Neeraj | LICHI |
| Isha | MANGO |
| Isha | MANGO |
| Isha | APPLE |
| Isha | ORANGE |
| Isha | LICHI |
| Gopal | MANGO |
| Gopal | MANGO |
| Gopal | APPLE |
| Gopal | APPLE |
| Gopal | APPLE |
| Gopal | ORANGE |
| Gopal | LICHI |
| Mayank | MANGO |
| Mayank | MANGO |
| Mayank | APPLE |
| Mayank | APPLE |
| Mayank | ORANGE |
| Mayank | LICHI |
Expected Output
| Name | MangoCount | APPLECount | LICHICount | ORANGECount |
| Gopal | 2 | 3 | 1 | 1 |
| Isha | 2 | 1 | 1 | 1 |
| Mayank | 2 | 2 | 1 | 1 |
| Neeraj | 3 | 1 | 3 | 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 FruitCount
(
Name VARCHAR(20)
,Fruit VARCHAR(25)
)
GO
--Insert Data
INSERT INTO FruitCount(Name,Fruit) VALUES
('Neeraj' ,'MANGO'),
('Neeraj' ,'MANGO'),
('Neeraj' ,'MANGO'),
('Neeraj' ,'APPLE'),
('Neeraj' ,'ORANGE'),
('Neeraj' ,'LICHI'),
('Neeraj' ,'LICHI'),
('Neeraj' ,'LICHI'),
('Isha' ,'MANGO'),
('Isha' ,'MANGO'),
('Isha' ,'APPLE'),
('Isha' ,'ORANGE'),
('Isha' ,'LICHI'),
('Gopal' ,'MANGO'),
('Gopal' ,'MANGO'),
('Gopal' ,'APPLE'),
('Gopal' ,'APPLE'),
('Gopal' ,'APPLE'),
('Gopal' ,'ORANGE'),
('Gopal' ,'LICHI'),
('Mayank' ,'MANGO'),
('Mayank' ,'MANGO'),
('Mayank' ,'APPLE'),
('Mayank' ,'APPLE'),
('Mayank' ,'ORANGE'),
('Mayank' ,'LICHI')
--Verify Data
SELECT Name,Fruit FROM FruitCount
|
UPDATE – 24-Apr-2015 – Solution 1
-- SELECT Name ,SUM(CASE WHEN Fruit='MANGO' THEN 1 ELSE 0 END) MangoCount ,SUM(CASE WHEN Fruit='APPLE' THEN 1 ELSE 0 END) APPLECount ,SUM(CASE WHEN Fruit='LICHI' THEN 1 ELSE 0 END) LICHICount ,SUM(CASE WHEN Fruit='ORANGE' THEN 1 ELSE 0 END) ORANGECount FROM FruitCount GROUP BY Name ` -- |
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
;WITH MANGO AS
(
SELECT NAME, COUNT(Name) OVER (PARTITION BY NAME) AS [#Mango] FROM FruitCount WHERE Fruit= ‘MANGO’
),
APPLE AS
(
SELECT NAME, COUNT(Name) OVER (PARTITION BY NAME) AS [#Apple] FROM FruitCount WHERE Fruit= ‘APPLE’
),
ORANGE AS
(
SELECT NAME, COUNT(Name) OVER (PARTITION BY NAME) AS [#Orange] FROM FruitCount WHERE Fruit= ‘ORANGE’
),
LICHI AS
(
SELECT NAME, COUNT(Name) OVER (PARTITION BY NAME) AS [#Lichi] FROM FruitCount WHERE Fruit= ‘LICHI’
)
SELECT DISTINCT FC.NAME, MANGO.#Mango, APPLE.#Apple, ORANGE.#Orange, LICHI.#Lichi
FROM FruitCount FC
INNER JOIN MANGO ON FC.NAME = MANGO.NAME
INNER JOIN APPLE ON FC.NAME = APPLE.NAME
INNER JOIN ORANGE ON FC.NAME = ORANGE.NAME
INNER JOIN LICHI ON FC.NAME = LICHI.NAME
LikeLike
with CTE
as
(
select *, count(Fruit) as [Count] from tblFruit
group by [Name], Fruit
)
select [Name], [Mango] as [MangoCount],
[Apple] as [AppleCount], [Lichi] as [LichiCount],
[Orange] as [OrangeCount]
from (select [Name], Fruit, [Count] from CTE) as C
pivot
(
sum([Count])
for Fruit in ([Mango], [Apple], [Lichi], [Orange])
) as PT
LikeLike
;WITH QUERY1
AS
(
SELECT NAME,SUM(CASE WHEN FRUIT=’MANGO’ THEN 1 ELSE 0 END) ‘MANGO’,
SUM(CASE WHEN FRUIT=’LICHI’ THEN 1 ELSE 0 END) ‘LICHI’,
SUM(CASE WHEN FRUIT=’APPLE’ THEN 1 ELSE 0 END) ‘APPLE’,
SUM(CASE WHEN FRUIT=’ORANGE’ THEN 1 ELSE 0 END) ‘ORANGE’
FROM FruitCount GROUP BY NAME
),
QUERY2
AS
(
SELECT NAME,COUNT(FRUIT)’TOTAL’ FROM FruitCount GROUP BY NAME
)
SELECT Q1.[Name], [MANGO],[LICHI],[APPLE],[ORANGE],[TOTAL] FROM QUERY1 Q1
INNER JOIN
QUERY2 Q2
ON Q1.NAME=Q2.NAME
LikeLiked by 1 person
SELECT Name
,COUNT(CASE WHEN Fruit=’MANGO’ THEN Fruit END) MANGOCOUNT
,COUNT(CASE WHEN Fruit=’APPLE’ THEN Fruit END) APPLECOUNT
,COUNT(CASE WHEN Fruit=’LICHI’ THEN Fruit END) LICHICOUNT
,COUNT(CASE WHEN Fruit=’ORANGE’ THEN Fruit END) ORANGECOUNT
FROM dbo.FruitCount
GROUP BY Name
LikeLike
select name, [Mango] as MangoCount, [Apple] as AppleCount, Lichi as [LichiCount], Orange as [OrangeCount] from
(select distinct *, count(name) over (partition by name,fruit order by name) as cnt from FruitCount) as x
pivot
(
Avg(cnt) for Fruit in ([Mango],[Apple],[Lichi],[Orange])
) as y
LikeLike