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.

18 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
  11. Dear Venkat,
    I would like to thank you for your relentless work to post this bioinformatic, educational and very practical SQL Server videos
    it is really related too the real working environment.
    What makes your videos is that you can delivery the subject matter in a very clear way. God Bless you

    ReplyDelete
  12. Thank You SO much Venkat sir.
    Your videos provides perfect guidelines and knowledge .

    ReplyDelete
  13. Thanks so much for putting so much effort into it, there could not have been more clear and brief explanation of such concepts.

    ReplyDelete
  14. great explanations you cleared my concept

    ReplyDelete
  15. Hello Dears,
    where I can find the script of the sql database to create my tables. or if I can download the .mdf to exercice the command inside each video chapter
    thanks for your help

    ReplyDelete
  16. if some one looking for table and sample data


    create table tblEmployee
    (
    id int identity ,
    name varchar(20),
    gender varchar(20),
    salary int,
    city varchar(20)
    )


    insert into tblEmployee values ('tom','male',4000,'london')
    insert into tblEmployee values ('pam','female',3000,'newyork')
    insert into tblEmployee values ('john','male',3500,'london')
    insert into tblEmployee values ('sam','male',4500,'london')
    insert into tblEmployee values ('tod','male',2800,'sydney')
    insert into tblEmployee values ('ben','male',7000,'newyork')
    insert into tblEmployee values ('sara','female',4800,'sydney')
    insert into tblEmployee values ('valarie','female',5500,'newyork')
    insert into tblEmployee values ('james','male',6500,'london')
    insert into tblEmployee values ('russel','male',8800,'london')

    ReplyDelete

It would be great if you can help share these free resources