
        
        
            
                
                
                    SELECT column_name(s)
FROM table_name
GROUP BY column
HAVING condition
ORDER BY column;

Examples

Consider the following table about whether students hold a drivers license:

student_id	full_name	age	has_license
1	Sky Towner	17	0
2	Ben Davis	19	1
3	Travis Apple	18	0
4	Arthur David	16	0
5	Benjamin Town	17	1

The above sample table can be created using the code here.

Basic usage

To find the ages where there is only one student of that age:


        
        
            
                
                
                    SELECT age, COUNT(*)
FROM drivers_license
GROUP BY age
HAVING COUNT(*) = 1;
                
            
            +------+----------+
| age  | COUNT(*) |
+------+----------+
|   19 |        1 |
|   18 |        1 |
|   16 |        1 |
+------+----------+

Note age 17 is not returned as there are two students who are 17.

Using HAVING with WHERE

To include a WHERE clause to filter out students with full_name='Sky Towner' before we perform grouping:


        
        
            
                
                
                    SELECT age, COUNT(*)
FROM drivers_license
WHERE full_name <> 'Sky Towner'
GROUP BY age
HAVING COUNT(*) = 1;
                
            
            +------+----------+
| age  | COUNT(*) |
+------+----------+
|   19 |        1 |
|   18 |        1 |
|   16 |        1 |
|   17 |        1 |
+------+----------+

This time we have 4 records returned as 'Sky Towner' is filtered out by the WHERE clause before we perform grouping. Therefore, of the records that were aggregated / grouped, 'Benjamin Town' was the only 17 year old, hence the HAVING clause was fulfilled and age 17 was returned.

Difference between HAVING and WHERE in MySQL

HAVING specifies what condition should be applied after the aggregation takes place, while WHERE specifies what records are eligible for grouping or aggregation in the first place. If a record does not meet the WHERE condition, it will not even be considered for GROUP BY and HAVING.

chevron_right