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:
We can get the required output by using this query: Select Name, Count(Name) from Names group by Name
The output will look like:
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.
(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
We can get this by two ways mentioned below:
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
- 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…