SQL groups: GROUP BY, HAVING and conditional counts

"How many orders did each restaurant get?" "Which customers ordered twice or more?" "What share of orders were cancelled?" These are the questions a business asks every day. After joins, grouping is the second big topic of a SQL round.

A weak answer groups the rows and then cannot say why the numbers are too high, or why a customer with no orders disappeared. A strong answer first says what one row of the answer means: "one row per restaurant per month". It then checks that a join did not repeat rows before it counts.

At SDE-1 level you should write GROUP BY with COUNT and SUM, and use HAVING. At SDE-2 level you should also write conditional counts, shares and duplicate checks, and know the trap of counting after a join.

Imagine a ballot box. First you sort the papers into piles, one pile for each party. Then you count each pile and write one line for each party. You started with many papers and ended with one line per pile.

SQL does the same. An aggregate function reads many rows and returns one value. GROUP BY makes the piles (the groups). The database then runs the aggregate on each pile and returns one row for each group. What one row of the result stands for is the grain, for example "one restaurant in one month".

Aggregate functions

• COUNT(*) counts rows.
• COUNT(column) counts the rows where that column is not NULL.
• COUNT(DISTINCT column) counts the different values that are not NULL.
• SUM, AVG, MIN and MAX work on the values that are not NULL.

All of them skip NULL (lesson 43). Without GROUP BY, the whole table is one group.

SELECT COUNT(*) AS orders, COUNT(coupon_off) AS with_coupon, COUNT(DISTINCT customer_id) AS customers,
  SUM(amount) AS total, ROUND(AVG(amount), 1) AS average, MIN(amount) AS smallest, MAX(amount) AS biggest
FROM orders;

orders  with_coupon  customers  total  average  smallest  biggest
------  -----------  ---------  -----  -------  --------  -------
    24            4          7   8260    344.2       150      640

There are 24 orders, but only 4 coupons, and customers counts 7 different customers because the guest order has a NULL customer_id. SUM of no rows is NULL, not 0. Use COALESCE(SUM(x), 0). SQLite also has TOTAL(x), which gives 0.0 for no rows.

SELECT SUM(amount) AS sum_of_none, COALESCE(SUM(amount), 0) AS safe_sum, TOTAL(amount) AS total_of_none FROM orders WHERE status = 'lost';

sum_of_none  safe_sum  total_of_none
-----------  --------  -------------
NULL                0            0.0

GROUP BY

GROUP BY takes one or more columns, or an expression. Rows with the same values go to the same group.

SELECT status, COUNT(*) AS orders, SUM(amount) AS money FROM orders GROUP BY status;

status     orders  money
---------  ------  -----
cancelled       3   1100
delivered      20   7010
placed          1    150

Twenty-four order rows fall into three piles by status. The delivered pile holds 20 rows, the cancelled pile holds 3 rows and the placed pile holds 1 row. An arrow from each pile leads to one result row: delivered with 20 orders, cancelled with 3 orders, and placed with 1 order. GROUP BY gives one result row for each pile.

SELECT strftime('%Y-%m', ordered_at) AS month, status, COUNT(*) AS orders
FROM orders GROUP BY strftime('%Y-%m', ordered_at), status ORDER BY month, status;

month    status     orders
-------  ---------  ------
2026-03  cancelled       2
2026-03  delivered      12
2026-04  cancelled       1
2026-04  delivered       5
2026-05  delivered       3
2026-05  placed          1

The rule: every column in SELECT must be in GROUP BY or inside an aggregate. Each group becomes one row, so a column that differs inside the group has no single value to show. Postgres and MySQL refuse such a query by default. SQLite does not. It picks the value from one row of the group, and it does not tell you.

SELECT status, customer_id, COUNT(*) AS orders FROM orders GROUP BY status;

status     customer_id  orders
---------  -----------  ------
cancelled            4       3
delivered            1      20
placed               2       1

The customer_id column is a guess of the database, and it can change when the data changes. Do not write this.

WHERE and HAVING

WHERE filters rows before grouping. HAVING filters groups after they are made, so it can use aggregates. The order of work is now:

1. FROM (and joins)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

SELECT restaurant_id, COUNT(*) AS orders, ROUND(AVG(amount), 1) AS average
FROM orders
WHERE status <> 'cancelled'
GROUP BY restaurant_id
HAVING AVG(amount) > 350
ORDER BY restaurant_id;

restaurant_id  orders  average
-------------  ------  -------
            1       3    493.3
            2       3    466.7

Cancelled orders are removed first, then the average of each restaurant is checked. A test on status belongs in WHERE, because it is about rows. A test on AVG belongs in HAVING, because it is about groups.

Counting after a join

Lesson 44 showed that a join to order_items repeats an order once for each line. COUNT(*) then counts lines, not orders. COUNT(DISTINCT o.id) counts orders. Summing o.amount also repeats, so we sum the lines instead.

SELECT o.restaurant_id, COUNT(*) AS lines, COUNT(DISTINCT o.id) AS orders,
  SUM(o.amount) AS wrong_money, SUM(i.qty * i.price) AS right_money
FROM orders AS o JOIN order_items AS i ON i.order_id = o.id
GROUP BY o.restaurant_id ORDER BY o.restaurant_id LIMIT 3;

restaurant_id  lines  orders  wrong_money  right_money
-------------  -----  ------  -----------  -----------
            1      7       4         3520         2080
            2      5       3         2440         1400
            3      4       2          760          380

Now a left join. We want the number of orders for every customer, even those with none. COUNT(*) counts the row with the NULL, so Dev would get 1. COUNT(o.id) skips NULL and gives the true 0.

SELECT c.name, COUNT(*) AS wrong, COUNT(o.id) AS orders
FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.id >= 7 GROUP BY c.id, c.name ORDER BY c.id;

name   wrong  orders
-----  -----  ------
Priya      2       2
Dev        1       0
Neha       1       0

Conditional counts

To count only the rows that match a rule, put a CASE inside SUM. The CASE gives 1 for a match and 0 for the rest, so the sum is the count.

SELECT restaurant_id,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
  ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancelled_percent
FROM orders GROUP BY restaurant_id ORDER BY restaurant_id;

restaurant_id  delivered  cancelled  cancelled_percent
-------------  ---------  ---------  -----------------
            1          3          1               25.0
            2          3          0                0.0
            3          2          0                0.0
            4          4          0                0.0
            5          2          2               50.0
            6          3          0                0.0
            7          3          0                0.0

A share is 100.0 * part / whole. The 100.0 keeps the decimals (lesson 42), and ROUND limits them. The same idea turns rows into columns, which is a pivot: one SUM(CASE ...) for each month. The cost: a new month needs a new column in the query.

SELECT restaurant_id,
  SUM(CASE WHEN strftime('%m', ordered_at) = '03' THEN amount ELSE 0 END) AS march,
  SUM(CASE WHEN strftime('%m', ordered_at) = '04' THEN amount ELSE 0 END) AS april,
  SUM(CASE WHEN strftime('%m', ordered_at) = '05' THEN amount ELSE 0 END) AS may
FROM orders WHERE status = 'delivered' GROUP BY restaurant_id ORDER BY restaurant_id;

restaurant_id  march  april  may
-------------  -----  -----  ---
            1   1080    400    0
            2    440    600  360
            3    380      0    0
            4    600    240  260
            5    680      0    0
            6    300    340  280
            7    720    330    0

To make buckets, use whole-number division. amount / 200 * 200 gives the lower end of a bucket of Rs 200: 340 becomes 200, and 640 becomes 600.

SELECT amount / 200 * 200 AS bucket_start, COUNT(*) AS orders FROM orders GROUP BY amount / 200 * 200 ORDER BY bucket_start;

bucket_start  orders
------------  ------
           0       2
         200      15
         400       4
         600       3

Finding duplicates

Group by the thing that must be unique and keep the groups with more than one row. Make the case equal first, or Asha@mail.test and asha@mail.test look different. NULLs form one group, so remove them first. We add a small table of sign-ups.

CREATE TABLE signups (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO signups VALUES (1, 'asha@mail.test'), (2, 'ravi@mail.test'), (3, 'Asha@Mail.test'), (4, 'meena@mail.test'), (5, 'ravi@mail.test'), (6, NULL), (7, NULL);

SELECT LOWER(email) AS email, COUNT(*) AS times
FROM signups WHERE email IS NOT NULL
GROUP BY LOWER(email) HAVING COUNT(*) > 1 ORDER BY email;

email           times
--------------  -----
asha@mail.test      2
ravi@mail.test      2

Top groups, and DISTINCT

For "the top 3 restaurants", sort by the aggregate and cut. Add a tie-break column so that the answer is the same every time.

SELECT restaurant_id, COUNT(*) AS delivered FROM orders WHERE status = 'delivered'
GROUP BY restaurant_id ORDER BY delivered DESC, restaurant_id LIMIT 3;

restaurant_id  delivered
-------------  ---------
            4          4
            1          3
            2          3

SELECT DISTINCT city FROM customers and SELECT city FROM customers GROUP BY city give the same rows. The second one is shown here (the first is in lesson 42).

SELECT city FROM customers GROUP BY city;

city
---------
Bengaluru
Delhi
Mumbai
Pune

Use DISTINCT to list values. Use GROUP BY when you also need an aggregate.

In Postgres and MySQL

• COUNT(*) FILTER (WHERE status = 'delivered') is a short form of the conditional count. SQLite 3.30 and Postgres have it. MySQL does not, so use SUM(CASE ...).
• Postgres and MySQL refuse a selected column that is not grouped. A column alias in HAVING works in MySQL and SQLite, but not in Postgres.
• Joining the text of a group: SQLite and MySQL use GROUP_CONCAT(x). Postgres uses STRING_AGG(x, ',').

SELECT restaurant_id, COUNT(*) FILTER (WHERE status = 'delivered') AS delivered FROM orders GROUP BY restaurant_id;

The owner wants four reports from the Tiffin Town rows. Reports 1 and 3 are new queries. Reports 2 and 4 repeat a query from above for one case.

Report 1: orders per restaurant per month. The grain is one row for each restaurant and month, so we group by both. We join restaurants to get names.

SELECT r.name AS restaurant, strftime('%Y-%m', o.ordered_at) AS month, COUNT(*) AS orders
FROM orders AS o JOIN restaurants AS r ON r.id = o.restaurant_id
GROUP BY r.id, r.name, strftime('%Y-%m', o.ordered_at)
ORDER BY r.name, month;

restaurant    month    orders
------------  -------  ------
Bombay Bites  2026-03       2
Bombay Bites  2026-04       1
Bombay Bites  2026-05       2
Chaat Corner  2026-03       2
Dosa Corner   2026-03       1
Dosa Corner   2026-04       1
Dosa Corner   2026-05       1
Misal Mane    2026-03       2
Misal Mane    2026-04       1
Roti Ghar     2026-03       1
Roti Ghar     2026-04       1
Roti Ghar     2026-05       1
Spice Route   2026-03       3
Spice Route   2026-04       1
Thali Tales   2026-03       3
Thali Tales   2026-04       1

Report 2: delivered against cancelled, in one query. The query above did it for all restaurants. Here it is for Thali Tales (restaurant 5), in one pass over the table, not two.

SELECT restaurant_id,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
  ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancelled_percent
FROM orders WHERE restaurant_id = 5 GROUP BY restaurant_id;

restaurant_id  delivered  cancelled  cancelled_percent
-------------  ---------  ---------  -----------------
            5          2          2               50.0

Report 3: repeat customers. A repeat customer has two or more delivered orders. The test is on a count, so it goes in HAVING.

SELECT customer_id, COUNT(*) AS delivered_orders
FROM orders WHERE status = 'delivered' AND customer_id IS NOT NULL
GROUP BY customer_id HAVING COUNT(*) >= 2 ORDER BY delivered_orders DESC, customer_id;

customer_id  delivered_orders
-----------  ----------------
          1                 5
          3                 4
          2                 3
          4                 2
          5                 2
          6                 2

Report 4: duplicate emails. The query above found asha@mail.test (ids 1 and 3, with different case) and ravi@mail.test (ids 2 and 5). It ignored the two NULL rows.

The predict box. For Dev, the group has one row, and o.id is NULL in it. So COUNT(*) is 1 and COUNT(o.id) is 0, as the earlier query shows. The right count of orders is 0.

Check by hand. Customer 1 has orders 1, 4, 10, 16 and 21. All are delivered. That is 5. Customer 3 has orders 3, 9, 13 and 18, all delivered, which is 4. This agrees with the result.

• It solves: one row of numbers for each group, from thousands of rows, in one pass over the table.
• It costs: the grain must be clear. A join before GROUP BY can repeat rows, and a wrong grouping column gives wrong numbers with no error. Grouping also reads every row of the group, so it is slow on a huge table without help.
• I would change it when: the same report is read all day. Then I keep the totals in a small summary table and update it, so the app does not read all the rows each time (lesson 35). The cost is extra work on every write.
• Say the grain before you write: "one row per restaurant per month".

"WHERE and HAVING are the same, so I can use either." WHERE runs before the groups exist, so it cannot use COUNT or AVG. HAVING runs after, so it can. Use WHERE for tests on rows, because it removes rows early.

"COUNT(*) and COUNT(column) give the same number." They differ when the column has NULLs. After a left join, COUNT(*) counts the row of a customer with no orders, and COUNT(o.id) does not.

"SQLite accepts the query, so the query is right." SQLite lets you select a column that is not grouped, and it shows one value from some row. Another database refuses it. Group by it, or put it in an aggregate.

1. Which clause can test COUNT(*) > 5: WHERE or HAVING? Which counts the rows of a left join wrongly for customers with no orders: COUNT(*) or COUNT(o.id)?
2. Delivered orders are 20 of 24 orders. Write the share as a percent with one decimal. Then say what 20 / 24 * 100 gives in SQLite.
3. A report shows "orders per restaurant" after a join with order_items, and restaurant 1 shows 7 orders, but it has 4 orders. What went wrong and how do you fix it?