• 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:

City Count (std)
Ahmedabad 45
Bhavnagar 20
Rajkot 15
Surat 21

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.