The HAVING Statement (learnsqlonline.org)

The HAVING statement is similar to the WHERE clause but operates on grouped data. It allows us to filter the results of a GROUP BY query based on conditions applied to aggregated values. This powerful statement enables more advanced and precise filtering in SQL queries.

--The HAVING statement
CREATE TABLE grades (name TEXT, class INTEGER, grade INTEGER);

INSERT INTO grades (name, class, grade) VALUES
    ("John", 1, 97), ("Eric", 1, 88), ("Jessica", 1, 98), ("Mike", 1, 82), ("Jeff", 1, NULL),
    ("Ben", 2, 93), ("Andrew", 2, 82), ("Jason", 2, 87), ("Carol", 2, 99), ("Fred", 2, 79);

SELECT class, AVG(grade) 
FROM grades 
WHERE grade > 85 
GROUP BY class;

The HAVING statement complements the GROUP BY clause by providing a way to filter groups based on aggregate functions such as SUM, AVG, COUNT, MAX, or MIN. It allows us to specify conditions that must be met by the aggregated values before the results are included in the final output.

--HAVING vs WHERE
CREATE TABLE grades (name TEXT, class INTEGER, grade INTEGER);

INSERT INTO grades (name, class, grade) VALUES
    ("John", 1, 97), ("Eric", 1, 88), ("Jessica", 1, 98), ("Mike", 1, 82), ("Jeff", 1, NULL),
    ("Ben", 2, 93), ("Andrew", 2, 82), ("Jason", 2, 87), ("Carol", 2, 99), ("Fred", 2, 79);

SELECT class, AVG(grade) 
FROM grades 
GROUP BY class
HAVING AVG(grade) > 90;

The HAVING statement follows the GROUP BY clause and precedes the ORDER BY clause in a SQL query. It uses comparison operators and logical operators to define the filtering conditions based on the aggregated values. Multiple conditions can be combined using logical operators.

The HAVING statement Exercise Solution

--Code Completed
CREATE TABLE grades (name TEXT, class INTEGER, grade INTEGER);

INSERT INTO grades (name, class, grade) VALUES
    ("John", 1, 97), ("Eric", 1, 88),
    ("Ben", 2, 93), ("Andrew", 2, 82), ("Jason", 2, 87), ("Carol", 2, 99), ("Fred", 2, 79),
    ("Steve", 3, 91), ("Bill", 3, 84), ("Barney", 3, 97);

 -- write your code here
SELECT class, MAX(grade) 
FROM grades 
GROUP BY class
HAVING COUNT(name) >= 3;

The HAVING statement is a valuable tool for analyzing data and extracting meaningful insights. It enables us to filter and retrieve specific groups based on aggregated values, providing deeper insights into our data.