- 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.