SQLite HAVING Clause

The HAVING clause in SQLite allows you to filter results based on aggregate functions after the GROUP BY clause. This can be incredibly useful for summarizing data and filtering grouped results to analyze your data.

To demonstrate the power of SQLite HAVING, we’ll use a sample table called orders with some sample data:

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  order_value INTEGER
);

INSERT INTO orders VALUES
  (1, 1, '2022-01-01', 100),
  (2, 2, '2022-01-15', 50),
  (3, 1, '2022-02-01', 200),
  (4, 3, '2022-02-15', 75),
  (5, 2, '2022-03-01', 60),
  (6, 1, '2022-03-15', 150),
  (7, 3, '2022-04-01', 125),
  (8, 2, '2022-04-15', 90);

This table tracks customer orders with an order ID, customer ID, order date, and order value.

Filtering Aggregated Results with SQLite HAVING

Let’s say we want to summarize the total order value for each customer but only show customers with a total order value greater than 250.

We can use GROUP BY and the SUM() aggregate to get totals per customer, then filter with HAVING:

SELECT
  customer_id,
  SUM(order_value) AS total_order_value  
FROM orders
GROUP BY customer_id
HAVING total_order_value > 250;

This would return:

customer_id | total_order_value
---------------------------
          1 |            450

Customer 1 is the only one with total orders over 250, so they are the only result.

Let’s add another example with an average instead of sum:

SELECT
  customer_id,
  AVG(order_value) AS average_order
FROM orders 
GROUP BY customer_id
HAVING average_order > 100;

This would return:

customer_id | average_order
---------------------------
          1 |     112.5
          3 |     100

Now we see Customer 1 and 3 have an average order value over 100.

The SQLite HAVING clause filters after aggregation, whereas WHERE filters before aggregation. This makes it perfect for filtering based on aggregated metrics.

Using SQLite HAVING without GROUP BY

You can also use the HAVING clause without GROUP BY. Let’s add a HAVING filter for orders after March 15, 2022:

SELECT
  order_id,
  order_date,
  order_value
FROM orders
HAVING order_date > '2022-03-15'; 

This would return all orders after March 15th:

order_id | order_date   | order_value
-------------------------------------
       6 | 2022-03-15   |       150
       7 | 2022-04-01   |       125
       8 | 2022-04-15   |        90

The HAVING clause filtered the results after retrieving all rows. This can be useful for filtering without aggregating.

Let’s showcase one more example using HAVING on a single column:

SELECT 
  order_id,
  order_value
FROM orders
HAVING order_value > 100;

This would return high value orders:

order_id | order_value
--------------------
       3 |        200  
       6 |        150
       7 |        125

Here, HAVING filtered for orders over 100 without any aggregation needed.

SQLite WHERE vs HAVING Clause

Summary

The HAVING clause is an incredibly useful tool for filtering aggregated results from a GROUP BY, or filtering rows after retrieval without aggregation. This provides immense flexibility in shaping your final filtered dataset.

With some SQL mastery, HAVING can help you better analyze and understand your data.