Having - SQL Programming

HAVING filters after grouping, so it can compare aggregate values such as monthly totals.
Program

Play the script to choose the minimum monthly total that appears in the report.
                            min_total
                            min_total = 150 (default)min_total = 100min_total = 220
                        

                            having_threshold.sql
                        
Replay: real traced execution (multi-file project)

                            CREATE TABLE invoices (invoice_id INTEGER, month TEXT, amount INTEGER);
INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);
WITH params(min_total) AS (VALUES (150)) SELECT month, SUM(amount) AS total FROM invoices GROUP BY month HAVING SUM(amount) >= (SELECT min_total FROM params) ORDER BY month;
CREATE TABLE invoices (invoice_id INTEGER, month TEXT, amount INTEGER);
INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);
WITH params(min_total) AS (VALUES (100)) SELECT month, SUM(amount) AS total FROM invoices GROUP BY month HAVING SUM(amount) >= (SELECT min_total FROM params) ORDER BY month;
CREATE TABLE invoices (invoice_id INTEGER, month TEXT, amount INTEGER);
INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);
WITH params(min_total) AS (VALUES (220)) SELECT month, SUM(amount) AS total FROM invoices GROUP BY month HAVING SUM(amount) >= (SELECT min_total FROM params) ORDER BY month;

                        
1tables ← 1 row1CREATE TABLE invoices (invoice_id INTEGER, month TEXT, amount INTEGER);2INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);⋯values this step1 rowtables
2invoices ← 5 rows1CREATE TABLE invoices (invoice_id INTEGER, month TEXT, amount INTEGER);2INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);3WITH params(min_total) AS (VALUES (150)) SELECT month, SUM(amount) AS total FROM invoices GROUP BY month HAVING SUM(amount) >= (SELECT min_total FROM params) ORDER BY month;values this step5 rowsinvoices
3result ← 2 rows⋯2INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);3WITH params(min_total) AS (VALUES (150)) SELECT month, SUM(amount) AS total FROM invoices GROUP BY month HAVING SUM(amount) >= (SELECT min_total FROM params) ORDER BY month;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE invoices (invoice_id INTEGER, month TEXT, amount INTEGER);2INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);⋯values this step1 rowtables
2invoices ← 5 rows1CREATE TABLE invoices (invoice_id INTEGER, month TEXT, amount INTEGER);2INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);3WITH params(min_total) AS (VALUES (100)) SELECT month, SUM(amount) AS total FROM invoices GROUP BY month HAVING SUM(amount) >= (SELECT min_total FROM params) ORDER BY month;values this step5 rowsinvoices
3result ← 2 rows⋯2INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);3WITH params(min_total) AS (VALUES (100)) SELECT month, SUM(amount) AS total FROM invoices GROUP BY month HAVING SUM(amount) >= (SELECT min_total FROM params) ORDER BY month;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE invoices (invoice_id INTEGER, month TEXT, amount INTEGER);2INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);⋯values this step1 rowtables
2invoices ← 5 rows1CREATE TABLE invoices (invoice_id INTEGER, month TEXT, amount INTEGER);2INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);3WITH params(min_total) AS (VALUES (220)) SELECT month, SUM(amount) AS total FROM invoices GROUP BY month HAVING SUM(amount) >= (SELECT min_total FROM params) ORDER BY month;values this step5 rowsinvoices
3result ← 1 row⋯2INSERT INTO invoices VALUES (1, '2026-01', 120), (2, '2026-01', 80), (3, '2026-02', 200), (4, '2026-02', 40), (5, '2026-03', 90);3WITH params(min_total) AS (VALUES (220)) SELECT month, SUM(amount) AS total FROM invoices GROUP BY month HAVING SUM(amount) >= (SELECT min_total FROM params) ORDER BY month;values this step1 rowresult
HAVING `HAVING` filters grouped rows after aggregate functions have been computed.
aggregate filter `SUM(amount) >= min_total` compares a monthly total, not a single row.
ORDER BY `ORDER BY month` keeps the report deterministic.