0%
DQDigital Qasas Courses/SQL for Professionals
Sign inAll courses
  • What SQL is, and why professionals learn it
  • Learn faster: pair this course with a free AI assistant
  • SELECT and FROM: your first real query
  • WHERE: keep only the rows you want
  • Combine conditions: AND, OR, IN, BETWEEN, LIKE, NULL
  • ORDER BY, LIMIT, and DISTINCT
  • COUNT, SUM, AVG, MIN, MAX
  • GROUP BY: one number per group
  • HAVING: filter the groups
  • Why tables relate: keys and joins
  • INNER JOIN: matching rows from two tables
  • LEFT JOIN: keep everything, find what is missing
  • Subqueries: a query inside a query
  • CASE: if-and-then inside a query
  • Window functions: a first look
  • Putting it together, and a cheat sheet
  • Practice challenges
Aggregate: turn rows into numbers

HAVING: filter the groups

Last updated 14 June 2026

WHERE filters rows before they are grouped. HAVING filters the groups after the aggregate is computed. The difference matters: you cannot put COUNT(*) > 1 in a WHERE, because the count does not exist yet at that stage.

SELECT customer_id, COUNT(*) AS n_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

That returns only customers who have placed more than one order. Run it:

Customers with more than one order.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT customer_id, COUNT(*) AS n_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

You can use both clauses together. Filter to paid orders first with WHERE, then keep only the busy customers with HAVING.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT customer_id, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2;

Your turn

Using order_items, find every product_id whose total quantity sold is 3 or more. Return product_id and the total named units.

Products that sold 3 or more units in total.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT product_id, SUM(quantity) AS units
FROM order_items
GROUP BY product_id
;
SELECT product_id, SUM(quantity) AS units FROM order_items GROUP BY product_id HAVING SUM(quantity) >= 3;
WHERE cannot see an aggregate, because it runs before the rows are grouped. COUNT(*) > 1 belongs in HAVING, which runs after. Putting it in WHERE is a syntax error, not a slow query.

You want only the groups where COUNT(*) is above 5. Which clause filters them?

  • HAVING, because it filters after grouping
  • WHERE, because it filters rows
  • Either one works the same way
WHERE filters individual rows before grouping, so the count does not exist yet. HAVING filters the groups after the aggregate is computed.
← PreviousGROUP BY: one number per groupNext →Why tables relate: keys and joins