Tags

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


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

http://MSBISkills.com

Advertisements