- 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.
SELECT <column_list>, [grouping function] FROM <table_name> [WHERE<condition>][ORDER BY<column_name>order] GROUP BY<column name>;
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.
- 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.
SELECT <column_list> grouping_function FROM <table_name> [ORDER BY <column name> ORDER] GROUP BY <column name> [HAVING <condition>];
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.