Clause	Description
`HAVING`	specifies what condition should be applied after the aggregation takes place.
`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`.


        
        
            
                
                
                    SELECT column_name(s)
FROM table_name
WHERE condition for records before grouping
GROUP BY column
HAVING condition for records after grouping;

Examples

Consider the following table about whether some 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.

HAVING

To only return 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 |
+------+----------+

Here we do not specify a WHERE clause hence all records are considered for grouping. We then use aggregate method COUNT to count the number of students of each age.

For ages where there is only one student of that age, we return the record. No record for age 17 is returned as there are two students (Sky Towner and Benjamin Town) who are 17.

Using HAVING with WHERE

To use 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 grouped, 'Benjamin Town' was the only 17 year old, hence the HAVING condition was fulfilled and age 17 was returned.

Published by Arthur Yanagisawa

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Ask a question or leave a feedback...

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!