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

Vals Data
a 1
a 5
c 9
c 7
b 6
b 4
b 6
d 1

Expected Output

Vals Cnts DifferFromMax
a 2 1
b 3 0
c 2 1
d 1 2

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”.

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