Level 8 of 13 · The counting club

SQL GROUP BY and HAVING Explained: COUNT, SUM, AVG

Squashing many rows into one answer with COUNT, SUM, AVG, MIN and MAX, grouping them with GROUP BY, and filtering the groups with HAVING.

Covers: COUNT · SUM · AVG · MIN · MAX · GROUP BY · HAVING

Practise GROUP BY

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# What is GROUP BY? GROUP BY puts rows that share a value into groups, then gives one answer per group: how many rows, their total, their average. Why you need it: many questions are about groups, not single rows. "How many customers live in each city?" and "how much has each customer spent?" are GROUP BY questions. - `GROUP BY city` one group, and one result row, per city - `COUNT(*)` how many rows are in each group The query answers the first question. The next slides cover counting and adding up, then the groups. > Most reports and dashboard charts are a GROUP BY query underneath.

SELECT city, COUNT(*)
FROM customers
GROUP BY city

# Summarise many rows into one Aggregate functions take many rows and return one value. - `COUNT(*)` how many rows - `SUM(…)` the values added up - `AVG(price)` the average - `MIN(price)` `MAX(price)` the smallest and the biggest > Aggregates skip NULL values (COUNT(*) is the exception). An AVG over a column with NULLs averages only the filled-in rows.

SELECT COUNT(*),
  AVG(price), MIN(price), MAX(price)
FROM pizzas

# COUNT(*) and COUNT(column) - `COUNT(*)` counts every row - `COUNT(tip)` counts only the rows where tip isn't NULL > To count the orders without a tip: COUNT(*) - COUNT(tip).

SELECT COUNT(*), COUNT(tip)
FROM orders

# Groups: GROUP BY GROUP BY puts rows with the same value into groups. The aggregate then runs once per group. - `GROUP BY city` one group per city - `COUNT(*)` counts the rows in each group > The result has one row per group. Customers in 4 cities give 4 rows.

SELECT city, COUNT(*)
FROM customers
GROUP BY city

# Filter groups: HAVING - `WHERE` filters rows, before they are grouped - `HAVING COUNT(*) > 1` filters groups, after they are counted > You can't use COUNT in a WHERE: the counting hasn't happened yet. Conditions on aggregates go in HAVING.

SELECT city, COUNT(*)
FROM customers
GROUP BY city
HAVING COUNT(*) > 1

# The GROUP BY rule Every column in the SELECT must be in the GROUP BY, or inside an aggregate. A city group holds two names: which one should it show? MySQL refuses with "isn't in GROUP BY". The practice database picks one at random, so don't copy this. - `--` a comment: the database ignores the rest of the line > MariaDB, which Appian Cloud uses, allows this query by default and returns any one of the names. Don't rely on it: the answer can change.

SELECT city, name, COUNT(*)
FROM customers
GROUP BY city;
-- MySQL: Expression #2 …
-- isn’t in GROUP BY

# Group by a calculated value - `DATE(ordered_at)` keeps only the day of a date and time - `AS day` names the result column - `ordered_at >= '2026-10-01' AND ordered_at < '2026-11-01'` every moment in October: from the 1st, up to but not including 1 November Each day becomes one group, so this counts the orders per October day. > GROUP BY the same expression you SELECT. MySQL also lets you write GROUP BY day, using the alias, but many databases don't.

SELECT DATE(ordered_at) AS day,
  COUNT(*) AS orders
FROM orders
WHERE ordered_at >= '2026-10-01'
  AND ordered_at < '2026-11-01'
GROUP BY DATE(ordered_at)

Appian corner

In Appian, use a!aggregationFields(groupings: a!grouping(field: …city), measures: a!measure(function: "COUNT", field: …id)). Measures can be COUNT, SUM, AVG, MIN or MAX. There is no HAVING, so filter the totals afterwards or put it in a database view.

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. The average pizza price.

SELECT ___(price) FROM pizzas

Show the answer

SELECT AVG(price) FROM pizzas

Short for average.

2. orders has 12 rows, and 5 of them have no tip (NULL). What does this return?
```
SELECT COUNT(tip) FROM orders
```
- 12
- 5
- 7
- 0
Show the answer
7. COUNT(tip) skips the NULLs: 12 − 5 = 7.
3. The cheapest and the most expensive pizza price, in one row.
Show the answer
```
SELECT MIN(price), MAX(price)
FROM pizzas
```
MIN(price), MAX(price)
4. How many customers live in each city?
Show the answer
```
SELECT city, COUNT(*)
FROM customers
GROUP BY city
```
GROUP BY city
5. The average tip for each order status.
Show the answer
```
SELECT status, AVG(tip)
FROM orders
GROUP BY status
```
GROUP BY status
6. Total pizzas ordered (the sum of qty) for each status.
Show the answer
```
SELECT status, SUM(qty)
FROM orders
GROUP BY status
```
SELECT status, SUM(qty) … GROUP BY status
7. How many orders were placed on each day of October 2026? Show the day and the count.
Show the answer
```
SELECT DATE(ordered_at), COUNT(*)
FROM orders
WHERE ordered_at >= '2026-10-01'
  AND ordered_at < '2026-11-01'
GROUP BY DATE(ordered_at)
```
WHERE keeps October (ordered_at >= '2026-10-01' AND ordered_at < '2026-11-01'), then GROUP BY DATE(ordered_at)
8. Why does MySQL refuse this?
```
SELECT status, customer_id, COUNT(*)
FROM orders
GROUP BY status
```
- COUNT(*) needs a column
- customer_id is neither grouped nor inside an aggregate
- status can’t be grouped
- GROUP BY must come before FROM
Show the answer
customer_id is neither grouped nor inside an aggregate. Each status pile holds many customer_ids, so which one should it show? Group by it too, or wrap it in COUNT, MIN, MAX…
9. Boss question! Each customer_id that placed more than 2 orders, with how many orders.
Show the answer
```
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 2
```
GROUP BY customer_id HAVING COUNT(*) > 2

Practise GROUP BY