Group by All – keyword in t-sql

There is a problem I came across some time back where I was having a table with names (including duplicates) in it and had to get name, count(name) as an output with a where clause to eliminate some of the entries. Major output requirement was to include the eliminated name entries too in the output and show name, 0 for those entries. Please find below the problem and solutions in detail:

Problem: There is a table having ID and Names like below and I need to return Name and Count of each Name.

Table: The table named Names looks like one provided below:

ID	Name
1	Sushant
2	Pandey
3	Sushant
4	Sushant
5	Scott
6	Scott
7	Pandey

We can get the required output by using this query: Select Name, Count(Name) from Names group by Name

The output will look like:

Name	Count
Sushant	3
Pandey	2
Scott	2

Now the real problem comes when we have to use a where clause which filters some entries. Clause is ‘where Name like ‘%S%’‘so if we use this clause in above query we would get the below result.

Name	Count
Sushant	3
Scott	2

(Query: Select Name, Count(Name) from Names where Name like ‘%S%’ group by Name).

So, going by the original requirement of including entry ‘Pandey’ also into the result showing count = 0 like

Name	Count
Sushant	3
Scott	2
Pandey	0

We can get this by two ways mentioned below:

1. Solution 1: We can use union to get the above result. Though it seems to have more computation involved in it.

   select Name,count(Name) as [count] from Names where Name in (select Name from Names where Name like ‘%S%’)group by Name union select Name, 0 as [count] from Names where Name not in (select Name from Names where Name like ‘%S%’)group by Name

2. Solution2:  There is a keyword for ‘Group By’ clause where we can use ‘All’ which make groups for all the entries even if they are filter out by a where clause. So the below query results in same output:  Select Name, Count(Name) from Names where Name like ‘%S%’ group by all Name
   

So, after messing a lot I found that the solution lied in just one line query so thought of posting. Might be helpful to you.

Bye for now…