SQL Puzzle | Get Difference between count and Maximum Count for each value – The SINGLE Select Puzzle

In this puzzle you have to get count for each Value and the difference between the maximum count of Value with their respective count. For more details please check the sample input and expected output.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE FindDifferenceWithMaxCount
(
	 Vals VARCHAR(100)
	,[Data] VARCHAR(100)
)
GO

INSERT INTO FindDifferenceWithMaxCount VALUES
('a',1),
('a',5),
('c',9),
('c',7),
('b',6),
('b',4),
('b',6),
('d',1)
GO

SELECT * FROM FindDifferenceWithMaxCount
GO


--

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

Solution – 1 | for SQL SERVER 2012-

--

SELECT Vals,COUNT(*) Cnts,MAX(COUNT(*)) OVER() - COUNT(*) DifferFromMax
FROM FindDifferenceWithMaxCount
GROUP BY Vals
GO 

--

Output-1

--                            

Vals                     Cnts        DifferFromMax
-------------------- --- ----------- -------------
a                        2           1
b                        3           0
c                        2           1
d                        1           2
                     
(4 rows affected)    

                                            
                                    
--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MsbiSkills.com