Home > DeveloperSection > Forums > what is the difference between having clause and group by statement in sql server
Ankit Singh

Total Post:341

Points:2389
Posted on    December-09-2015 9:08 PM

 Database SQL Server  SQL  SQL Server 2008  SQL Server 2012 
Ratings:


 1 Reply(s)
 344  View(s)
Rate this:
I want to know what is the difference between having clause and  group by in sql server please tell me.


aditya kumar Patel

Total Post:246

Points:1752
Posted on    December-09-2015 9:20 PM

In SQL, the having clause and the group by statement work together when using aggregate functions like SUM, AVG, MAX, etc.

If we want to calculate the total salary that each employee has received, then we would write a SQL statement like this:

select Emp_Name, sum(Emp_Sal) as TotalSalary from Employee group by Emp_Name;

In the SQL statement above, you can see that we use the "group by" clause with the employee column. The group by clause allows us to find the sum of the salary for each employee.

Now, suppose we wanted to find the employees who received more than 1,0000  salary – this is assuming of course that the Employee table contains salary. This is when we need to use the HAVING clause to add the additional check to see if the sum of salary is greater than 1,0000 and this is what the SQL look like:


select Emp_Name, sum(Emp_Sal)as TotalSalary from Employee group by Emp_Name

having sum(Emp_Sal) > 10000;

So, from the example above, we can see that the group by clause is used to group column(s) so that aggregates (like SUM, MAX, etc) can be used to find the necessary information. The having clause is used with the group by clause when comparisons need to be made with those aggregate functions – like to see if the SUM is greater than 1,000, as in our example above.


Modified On Jan-27-2016 06:22:16 AM

Don't want to miss updates? Please click the below button!

Follow MindStick