Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Group By - Part 11

In SQL Server we have got lot of aggregate functions. Examples
1. Count()
2. Sum()
3. avg()
4. Min()
5. Max()

Group by clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expressions. It is always used in conjunction with one or more aggregate functions.









I want an sql query, which gives total salaries paid by City. The output should be as shown below.


Query for retrieving total salaries by city:
We are applying SUM() aggregate function on Salary column, and grouping by city column. This effectively 
adds, all salaries of employees with in the same city.
Select City, SUM(Salary) as TotalSalary 
from tblEmployee
Group by City

Note: If you omit, the group by clause and try to execute the query, you get an error - Column 'tblEmployee.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 

Now, I want an sql query, which gives total salaries by City, by gender. The output should be as shown below.



Query for retrieving total salaries by city and by gender: It's possible to group by multiple columns. In this query, we are grouping first by city and then by gender. 
Select City, Gender, SUM(Salary) as TotalSalary
from tblEmployee
group by City, Gender

Now, I want an sql query, which gives total salaries and total number of employees by City, and by gender. The output should be as shown below.



Query for retrieving total salaries and total number of employees by City, and by gender: The only difference here is that, we are using Count() aggregate function.
Select City, Gender, SUM(Salary) as TotalSalary,
COUNT(ID) as TotalEmployees

from tblEmployee
group by City, Gender

Filtering Groups:
WHERE clause is used to filter rows before aggregation, where as HAVING clause is used to filter groups after aggregations. The following 2 queries produce the same result.

Filtering rows using WHERE clause, before aggrgations take place:
Select City, SUM(Salary) as TotalSalary
from tblEmployee
Where City = 'London'
group by City

Filtering groups using HAVING clause, after all aggrgations take place:
Select City, SUM(Salary) as TotalSalary
from tblEmployee
group by City
Having City = 'London'

From a performance standpoint, you cannot say that one method is less efficient than the other. Sql server optimizer analyzes each statement and selects an efficient way of executing it. As a best practice, use the syntax that clearly describes the desired result. Try to eliminate rows that 
you wouldn't need, as early as possible.

It is also possible to combine WHERE and HAVING
Select City, SUM(Salary) as TotalSalary
from tblEmployee
Where Gender = 'Male'
group by City
Having City = 'London'

Difference between WHERE and HAVING clause:
1. WHERE clause can be used with - Select, Insert, and Update statements, where as HAVING clause can only be used with the Select statement.
2. WHERE filters rows before aggregation (GROUPING), where as, HAVING filters groups, after the aggregations are performed.
3. Aggregate functions cannot be used in the WHERE clause, unless it is in a sub query contained in a HAVING clause, whereas, aggregate functions can be used in Having clause.

12 comments:

  1. How come its possible that you explain everything without even allowing me to ask question...
    You answer all my questions before i ask...
    I would like to extent my sincere thanks to you.
    Please do continue...

    PS.If you can take class for explaining SAS software, please do let me know...I am using in my office and find it difficult..hope you can help me ...thank you a lot....
    Please send me reply to vjp2082@yahoo.co.in

    ReplyDelete
  2. Hello Mr, I'm very thankful for your video and tutorial,
    but, can I get the data dictionary of every table?
    I got a little confuse about tblEmployee. Where can I find the design of the table?
    thank you =)

    ReplyDelete
  3. I am a junior C#, ASP.NET and SQL server learner at the moment and I have been purchasing books the past few months to lean these subjects. However, the books have not really helped to explain clearly as you do.
    You are extremely good ! Your videos and slides are making my dream clearer.

    Thank you and please continue the good JOB !!

    ReplyDelete
  4. Dear Sir
    I am learning SQL for the couple of months. I have seen a lot of materials but yours is the best. Even though I don't know u in person, u earned a lot of respect from me. providing this quality material for free is really amazing. u are changing the life of many people including mine in a good way .
    can u cover more topis in SQL like: Backup and Recovery, HA/DR like Mirroring Logshipping,Replication and Cluster, Security,SSIS/SSRS
    Thank you Sir
    From DC

    ReplyDelete
  5. THANK SO VERY MUCH FOR POSTING ALL YOUR VIDEOS AND FOR DOING SUCH AN EXCELLENT JOB EXPLAINING THE CONCEPTS. YOU ARE TRULY AMAZING!!! -- GOD BLESS YOU!

    ReplyDelete
  6. Venkat your tutorials and videos are awesome.....

    ReplyDelete
  7. thank u for such a beneficial, valuable videos ..... its very easy to learn for the biggner with the help of these tutorials .

    ReplyDelete
  8. hey venkat
    believe me or not i started coding in .net after seeing your videos. My husband got impressed. I really like to pay something once i get the job. I seen the link to amazon but could you have one more button for donation to some NGO or something like that... I am sure many people like to do that after watching your noble task of contributing to society.

    ReplyDelete
  9. Hi Venkat,ur videos are awesome.I have one doubt about this statement: WHERE clause can be used with - Select, Insert, and Update statements, where as HAVING clause can only be used with the Select statement.Does where work with Insert?or u want say delete, instead of insert

    ReplyDelete
  10. Hi venkat, i need to know if we can use WHERE Clause in INSERT Statement, because i think WHERE can only be used while UPDATING an existing data. But while INSERTING, it will completely be a new record where "WHERE" clause will be of no use. I have seen that it can be used in "Insert Into Select Statement" but that's completely different than only INSERT statement. So, please let us know once you get this message. I love your videos a lot but have a little confusion. :)

    ReplyDelete
    Replies
    1. Where clause can be used only with select, update and delete.It cannot be used with insert unless we use where not exists. This is a different case. Thanks for noticing. :)

      Delete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.