MySQL GROUP BY and HAVING Clause with Examples

What is the SQL GROUP BY Clause?

The GROUP BY clause is a SQL command that is used to group rows that have the same values. It is written inside the SELECT statement, and it is normally used together with aggregate functions to produce summary reports from the database.

That is what it does: it summarizes data held in the database. Queries that contain the GROUP BY clause are called grouped queries, and they return a single row for every grouped item.

SQL GROUP BY Syntax

Now that the purpose of the clause is clear, look at the syntax of a basic grouped query.

SELECT statements... GROUP BY column_name1[, column_name2, ...] [HAVING condition];

HERE

• “SELECT statements…” is the standard SQL SELECT command query.
• “GROUP BY column_name1” is the clause that performs the grouping based on column_name1.
• “[, column_name2, …]” is optional and represents other column names when the grouping is done on more than one column.
• “[HAVING condition]” is optional and is used to restrict the rows affected by the GROUP BY clause. It is similar to the WHERE clause, except that it is applied after the grouping.

Grouping Using a Single Column

The quickest way to see the effect of the SQL GROUP BY clause is to compare an ungrouped query with a grouped one. Start with a simple query that returns every gender entry in the members table.

SELECT `gender` FROM `members`;

Nine rows are returned, and every value is repeated. Suppose we want the unique values for gender instead. The query below adds the GROUP BY clause.

SELECT `gender` FROM `members` GROUP BY `gender`;

Executing the above script in MySQL Workbench against the myflixdb gives us the following results.

Note that only two rows have been returned, because the table holds only two gender types. The GROUP BY clause grouped all the “Male” members together and returned a single row for them, and it did the same with the “Female” members.

Grouping Using Multiple Columns

Grouping on one column is often too coarse for a real report. GROUP BY accepts a comma-separated list of columns, and the combination of their values defines each group.

Suppose that we want a list of movie category_id values and the corresponding years in which the movies were released. Observe the output of this simple query first.

SELECT `category_id`, `year_released` FROM `movies`;

The highlighted rows show that the result contains duplicates. Executing the same query with GROUP BY removes them.

SELECT `category_id`, `year_released` FROM `movies` GROUP BY `category_id`, `year_released`;

Executing the above script in MySQL Workbench against the myflixdb gives us the following results shown below.

The GROUP BY clause operates on both category_id and year_released to identify unique rows. The two duplicate rows for category 6 in 2007 collapsed into one.

Rule of thumb: if the category id is the same but the year released is different, the row is treated as unique. If the category id and the year released are the same for more than one row, the rows are duplicates and only one of them is shown.

Grouping and Aggregate Functions

Removing duplicates is useful, but the real power of grouping appears when it is paired with aggregate functions. An aggregate function calculates one value for each group: COUNT counts rows, SUM adds values, and AVG, MIN, and MAX describe the spread.

Suppose we want the total number of male and female members in the database. The script below does that.

SELECT `gender`, COUNT(`membership_number`) FROM `members` GROUP BY `gender`;

Executing the above script in MySQL Workbench against the myflixdb gives us the following results.

The rows are grouped by every unique gender value, and the number of rows inside each group is counted by the COUNT aggregate function. The nine member records collapse into two summary rows.

Restricting Query Results Using the HAVING Clause

Groupings are not always wanted for every row in a table. Sometimes the report must be restricted to a given criterion, and that is the job of the HAVING clause.

Suppose we want to know all the release years for movie category id 8. The script below achieves that result.

SELECT * FROM `movies` GROUP BY `category_id`, `year_released` HAVING `category_id` = 8;

Executing the above script in MySQL Workbench against the myflixdb gives us the following results shown below.

Only the movies with category id 8 have been kept by the HAVING condition.

Warning: MySQL 5.7 and later enable the ONLY_FULL_GROUP_BY mode by default, and under that mode SELECT * with a GROUP BY clause is rejected, because movie_id, title, and director are neither grouped nor aggregated. In production, name the grouped columns explicitly, for example SELECT category_id, year_released FROM movies GROUP BY category_id, year_released HAVING category_id = 8;

WHERE vs HAVING vs GROUP BY vs ORDER BY

Beginners frequently mix these four clauses, because all of them shape the result set. The difference lies in when MySQL applies them: WHERE runs before the rows are grouped, HAVING runs after, and ORDER BY runs last of all.

The practical consequence is a performance one. Filtering with WHERE removes rows before the grouping work starts, so a condition that does not depend on an aggregate result belongs in WHERE rather than HAVING.

FAQs