SQLite - HAVING Clause

SQLite - The HAVING Clause

The HAVING clause is used in combination with the GROUP BY clause to filter the results of an aggregate function. It acts like a WHERE clause, but for groups instead of individual rows.

WHERE vs. HAVING: A Clear Distinction

This is a common point of confusion. The key difference is the order of operations in a query:

• The WHERE clause filters rows before they are grouped. It operates on individual row data.
• The HAVING clause filters groups after they have been created by GROUP BY. It operates on the result of aggregate functions (like COUNT(), SUM(), AVG()).

Syntax

The HAVING clause must appear after the GROUP BY clause and before any ORDER BY clause.

SELECT
  column_name(s),
  aggregate_function(column_name)
FROM table_name
WHERE [ individual row condition ]
GROUP BY column_name(s)
HAVING [ group condition based on aggregate function ]
ORDER BY column_name(s);

Practical Example

Let’s consider a products table with the following records:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    category TEXT,
    price REAL,
    stock_quantity INTEGER
);

INSERT INTO products (name, category, price, stock_quantity) VALUES
('Laptop', 'Electronics', 1200.00, 10),
('Mouse', 'Electronics', 25.00, 150),
('Keyboard', 'Electronics', 75.00, 70),
('Desk Chair', 'Furniture', 150.00, 30),
('Coffee Table', 'Furniture', 220.00, 15),
('T-Shirt', 'Apparel', 20.00, 200),
('Jeans', 'Apparel', 60.00, 80);

Problem: Find categories that have more than one product.

We need to group by category and then count the number of products in each group. Finally, we filter these groups to keep only those where the count is greater than 1.

SELECT
  category,
  COUNT(id) AS product_count
FROM products
GROUP BY category
HAVING COUNT(id) > 1;

This will produce the following result:

category     product_count
-----------  -------------
Apparel      2
Electronics  3
Furniture    2

Problem: Find categories where the average price of products is over $100.

Here, we group by category, calculate the average price for each, and then use HAVING to filter based on that average.

SELECT
  category,
  AVG(price) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) > 100.00;

This will produce the following result:

category     average_price
-----------  ----------------
Electronics  433.333333333333
Furniture    185.0

Combining WHERE and HAVING

Let’s find categories where the average price of products (costing more than $50) is over $100.

SELECT
  category,
  AVG(price) as average_price
FROM products
WHERE price > 50.00  -- Filter individual rows first
GROUP BY category
HAVING AVG(price) > 100.00; -- Filter groups after aggregation

This query first excludes the ‘Mouse’ and ‘T-Shirt’ from the calculation, then groups and filters the groups.

category     average_price
-----------  ----------------
Electronics  637.5
Furniture    185.0

Common Mistakes

Using WHERE for aggregate functions: The following query will fail because WHERE cannot operate on the result of COUNT().

-- INCORRECT
SELECT category, COUNT(id)
FROM products
GROUP BY category
WHERE COUNT(id) > 1; -- ERROR!

Using HAVING for non-aggregated conditions: While some databases allow this, it’s poor practice and less efficient. Use WHERE for filtering individual rows.

-- INEFFICIENT (though may work in SQLite)
SELECT category, AVG(price)
FROM products
GROUP BY category
HAVING category = 'Electronics';

-- CORRECT & EFFICIENT
SELECT category, AVG(price)
FROM products
WHERE category = 'Electronics'
GROUP BY category;