Home / mySQL / Difference between having clause and group by statement

Difference between having clause and group by statement

GROUP BY

The group by clause is used for grouping the records of the database table according to our need. Which is an optional part of SELECT statement.

SYNTAX

SELECT column1, column2
FROM table_name
WHERE [ conditions ] GROUP BY column1, column2
ORDER BY column1, column2




The GROUP BY clause must appear after the FROM and WHERE clauses

Example of Group by in a Statement. Consider following Employee table




Employee_id Employee_name salary
1 Appu 9000
2 Ammu 8000
3 Achu 6000
4 Arathy 10000
5 Ashwathi 8000

Here we want to find name of employees grouped by their salaries. SQL query for the above requirement will be,

SELECT Employee_name from Employee group by salary

The Result will be,

Name
Achu
Ammu
Appu
Arathy

Example of Group by in a Statement with WHERE clause

Employee_id Employee_name salary
1 Appu 9000
2 Ammu 8000
3 Achu 6000
4 Arathy 10000
5 Ashwathi 8000

SQL query will be,

select Employee_name, salary from Employee where Employee_name=’ Ashwathi’ mployee group by salary;

The result will be

Employee_name salary
Ashwathi 8000

NOTE: Group By clause will always come at the end, just like the Order by clause.




HAVING

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used.

SYNTAX

select column_name, function(column_name)
FROM table_name
WHERE column_name condition
GROUP BY column_name
HAVING function(column_name) condition

Example

Employee_id Employee_name salary
1 Appu 2000
2 Ammu 1000
3 Achu 2000
4 Arathy 1000
5 Appu 2000

Suppose we want to find the Employee_name whose salary sum is more than 3000. We will use the below SQL query,

SELECT * from Employee group Employee_name having sum(salary) > 3000

Result will be,

Employee_id Employee_name salary
1 Appu 2000

If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

Loading Facebook Comments ...
Social Media Auto Publish Powered By : XYZScripts.com