Group By - Having Clause
☞ Following are the topics to be covered :
1. GROUP BY Clause.
2. HAVING Clause.
GROUP BY Clause
☞ It is used to collect data from multiple tuples/records/rows that have identical values in a particular field/attribute/column.
☞ It is used with aggregate functions.
☞ Syntax :
SELECT * FROM <table_name>
WHERE <column_name> = <value>
GROUP BY <column_name>;
OR
SELECT <aggregate_function>
FROM <table_name>
GROUP BY <column_name>;
☞ Example :
SELECT gender, SUM(fees) FROM Student
GROUP BY gender;
OR
SELECT gender, SUM(fees), COUNT(fees)
FROM Student
GROUP BY gender;
HAVING Clause
☞ It is used to place a condition on group i.e. data generated by GROUP BY clause.
☞ It works like a WHERE clause.
☞ WHERE clause places a condition on a single row.
☞ Syntax :
SELECT <aggregate_function>
FROM <table_name>
HAVING <condition>;
☞ Example :
SELECT gender, SUM(fees), COUNT(fees)
FROM Student
HAVING COUNT(fees)>2;
OR
SELECT gender, SUM(fees), COUNT(fees)
FROM Student
HAVING COUNT(fees)>2OR SUM(fees)<40000;
OR
SELECT gender, SUM(fees), COUNT(fees)
FROM Student
HAVING COUNT(fees)>2AND SUM(fees)>40000;
