what is the difference between having clause and group by statement in sql server
I want to know what is the difference between having clause and group by in sql server please tell me.
Ask by Anonymous User
Updated 09 Dec 2015
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:
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.