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…

Advertisements

2 Responses to Group by All – keyword in t-sql

  1. Yuwono says:

    HAPPY NEW YEAR…
    Very interesting article whom you post.
    Here I can read about feature and product reviews into additional knowledge for me and I am very grateful to the author of this blog. http://www.computerriver.com

  2. best keyword tools…

    […]Group by All – keyword in t-sql « Sushantp’s Weblog[…]…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Who am I what am I doing?

Who am I what am I doing?

%d bloggers like this: