SQL has different types of operations used to make queries to a database. SQL’s HAVING statement works like a subquery on top of an initial query. It works well when you have an overwhelming amount of entries that have one value in common and you want to filter it further. In this article, we take a look to see how it works.
Preparation
In this SQL Fiddle using MySQL, I’ve created a sample schema used for these examples.
create table Cars ( id INT PRIMARY KEY, year VARCHAR(50), car_make VARCHAR(50), car_model VARCHAR(50) );
This creates a table called “Cars” and has a schema that looks at each car’s make model and year. The next block inserts values into those columns:
insert into Cars (id, year, car_make, car_model) values (1, 2011, 'Mazda', 'Miata MX-5'); insert into Cars (id, year, car_make, car_model) values (2, 1969, 'Ford', 'Mustang'); insert into Cars (id, year, car_make, car_model) values (3, 2007, 'Toyota', '4Runner'); insert into Cars (id, year, car_make, car_model) values (4, 2013, 'Porsche', '911'); insert into Cars (id, year, car_make, car_model) values (5, 1991, 'Buick', 'Coachbuilder'); insert into Cars (id, year, car_make, car_model) values (6, 2007, 'Kia', 'Sportage'); insert into Cars (id, year, car_make, car_model) values (7, 1997, 'Oldsmobile', 'Cutlass Supreme'); insert into Cars (id, year, car_make, car_model) values (8, 2003, 'BMW', '7 Series'); insert into Cars (id, year, car_make, car_model) values (9, 1996, 'Ford', 'F150'); insert into Cars (id, year, car_make, car_model) values (10, 1992, 'Suzuki', 'SJ'); insert into Cars (id, year, car_make, car_model) values (11, 2001, 'Jeep', 'Grand Cherokee'); insert into Cars (id, year, car_make, car_model) values (12, 2000, 'Ford', 'F250'); insert into Cars (id, year, car_make, car_model) values (13, 2003, 'Honda', 'Insight'); insert into Cars (id, year, car_make, car_model) values (14, 2006, 'Chevrolet', 'HHR Panel'); insert into Cars (id, year, car_make, car_model) values (15, 1987, 'Mercedes-Benz', 'S-Class'); insert into Cars (id, year, car_make, car_model) values (16, 2004, 'Chevrolet', 'SSR'); insert into Cars (id, year, car_make, car_model) values (17, 1990, 'Maserati', '228'); insert into Cars (id, year, car_make, car_model) values (18, 2005, 'Saturn', 'Ion'); insert into Cars (id, year, car_make, car_model) values (19, 1987, 'Audi', '5000CS'); insert into Cars (id, year, car_make, car_model) values (20, 1999, 'Chevrolet', 'S10'); insert into Cars (id, year, car_make, car_model) values (21, 2007, 'Jeep', 'Liberty'); insert into Cars (id, year, car_make, car_model) values (22, 2002, 'Lamborghini', 'Murciélago'); insert into Cars (id, year, car_make, car_model) values (23, 2000, 'Hyundai', 'Tiburon'); insert into Cars (id, year, car_make, car_model) values (24, 2011, 'Jeep', 'Patriot'); insert into Cars (id, year, car_make, car_model) values (25, 1985, 'Pontiac', 'Sunbird');
If you would like to mock your own data for this exercise, I recommend using mockaroo.com to do that. You can structure your database schema pretty simply using this method.
How to Use HAVING
SELECT COUNT(id), car_make FROM Cars GROUP BY car_make HAVING COUNT(id) > 0;
Use HAVING when you need to use an aggregate function and the WHERE clause can’t be used. An aggregate functions are those functions that perform some sort of operation and return a single value. Think COUNT, AVG, or SUM. The code above returns:
| COUNT(id) | car_make |
| 1 | Audi |
| 1 | BMW |
| 1 | Buick |
| 3 | Chevrolet |
| 3 | Ford |
| 1 | Honda |
| 1 | Hyundai |
| 3 | Jeep |
| 1 | Kia |
| 1 | Lamborghini |
| 1 | Maserati |
| 1 | Mazda |
| 1 | Mercedes-Benz |
| 1 | Oldsmobile |
| 1 | Pontiac |
| 1 | Porsche |
| 1 | Saturn |
| 1 | Suzuki |
| 1 | Toyota |
We use a select statement to grab the sql COUNT of car_makes that exist and a group by clause to separate them by car_make.
That’s all there is to it! As a reminder, the HAVING clause in an SQL query is a subfilter that uses an aggregate function to perform some additional query work on a database.
Experiment with other aggregate functions to increase knowledge of this unique clause!
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.
