GROUP BY

• A group by clause is used to create groups based on the similar records.
• It first sort records on the group by column and then creates different groups.
• It creates a separate group for null records.
• A group by clause comes after FROM clause.

Syntax:

SELECT <column_list>, [grouping function] FROM <table_name>
       [WHERE<condition>][ORDER BY<column_name>order]
       GROUP BY<column name>;

Example:

SELECT city, count (city) FROM student GROUP BY city ORDER BY city;

• We must use at least one grouping function with the group by clause such as count, sum, max, min etc.
• The above example will create groups base on similar city and display total no. of records within each group as follow.

Output:

Having Clause

• Having clause is used to filter records base on sum specific condition.
• It is similar to a where
• The main different between where clause and having clause is that where clause filter records on individual row while having clause filter records on the group.
• Having clause can be use only with the group by clause.

Syntax:

SELECT <column_list> grouping_function FROM <table_name>
[ORDER BY <column name> ORDER] GROUP BY <column name>
[HAVING <condition>];

Example:

SELECT city count (city) FROM student GROUP BY city HAVING count (city)>=5;

• The above command will display only group of those cities that have at list 5 or more records available.