GROUP BY & Aggregates

A database table is a long list of individual facts, but the questions people ask are rarely about one row. How much did we sell last month? Which customer orders the most? How many orders are still pending? Each of these needs the database to look at many rows and boil them down to a single number or a short summary table. That is exactly what aggregate functions and GROUP BY are for, and they sit behind almost every report, dashboard and analytics query you will ever meet.

In this chapter everything is built around one small orders table, so you can keep the whole data set in your head and check every result by eye. You will start with the basic aggregates (COUNT, SUM, AVG, MIN and MAX), then see why COUNT(*) and COUNT(col) can give different answers once NULL values appear. From there you will split rows into groups with GROUP BY, group on more than one column, and learn when to filter with WHERE and when with HAVING.

By the end you will be able to write a grouped summary query from scratch, predict its result before you run it, and explain why a query fails or quietly returns the wrong numbers. You will also know how to aggregate across joined tables without double-counting, and you will have a short list of the classic mistakes to check for, plus a cheat sheet to keep beside you.

Most queries you have written so far return one result row for every table row that matches. Aggregation works differently. It reads many rows and collapses them into a single summary value, such as how many orders exist or how much money they add up to. When you apply an aggregate to a whole table, the answer is always exactly one row, no matter whether the table holds six rows or six million.

Every section of this chapter uses the same small table, so a number you see in one section means the same thing in the next. It is called orders and has five columns: id, customer, city, amount and status. It holds six rows. Two of them are deliberately incomplete: order 5 has a NULL amount (the price is not known yet) and order 6 has a NULL city (we never recorded where Kiran lives). These gaps matter later, when we look at how aggregates treat NULL.

An aggregate function takes a column (or the special *, meaning whole rows) and returns one scalar, a single value. SQL gives you five core ones, and the rest of the chapter is built from them.

Let us combine two of them in one query: SELECT COUNT(*), SUM(amount) FROM orders;. COUNT(*) counts all six rows. SUM(amount) adds 100 + 250 + 150 + 50 + 200 and ignores the NULL, giving 750. The program below builds the sample table in an in-memory SQLite database and runs that query. It also runs a plain SELECT customer for contrast, so you can see six rows come back from the plain query but only one from the aggregate.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER, customer TEXT, city TEXT, amount INTEGER, status TEXT)")
db.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", [
    (1, "Asha", "Pune", 100, "paid"),
    (2, "Ravi", "Delhi", 250, "paid"),
    (3, "Asha", "Pune", 150, "refunded"),
    (4, "Meena", "Delhi", 50, "paid"),
    (5, "Ravi", "Delhi", None, "pending"),
    (6, "Kiran", None, 200, "paid"),
])

plain = db.execute("SELECT customer FROM orders").fetchall()
summary = db.execute("SELECT COUNT(*), SUM(amount) FROM orders").fetchall()

print("plain rows:", len(plain))
print("aggregate rows:", len(summary))
print("result:", summary[0])

Build the sample table and run the first aggregate

Why does the query above return a single row? Because without GROUP BY, SQL treats the whole table as one single group. Every row belongs to that one group, the aggregates are computed over all of them, and one summary row comes out. Later in the chapter, GROUP BY will let you split the table into several groups instead, and you will get one summary row per group. For now, just remember that an aggregate with no GROUP BY means one big group.

This leads to a rule that trips up many beginners. An aggregate query returns one value per group, but a plain column such as customer has a different value on every row. If you write SELECT customer, SUM(amount) FROM orders; there is only one group, so SQL has no way to choose which of the six customer names should sit beside the total. A strict database rejects the query with an error. A lenient one, such as SQLite or old MySQL settings, quietly picks an arbitrary row's name, which is worse because the result looks believable but is misleading.

In short: if a column is inside an aggregate or listed in GROUP BY, it is allowed beside other aggregates. Otherwise you get an error or an arbitrary value. The fix for customer is to group by it, which is exactly what the section on grouping on one column shows later in this chapter.

Most questions you ask a table end in a number: how many orders, how much money, what is typical. Three aggregate functions answer them. COUNT counts, SUM adds up numeric values, and AVG returns the mean. Each one collapses many rows into a single value.

The examples all use the small orders table from the previous section. It has six rows. One of them, order 6, has a NULL amount, meaning the amount is unknown.

The key habit is that aggregates skip NULL. The sum is 100 + 250 + 150 + 50 + 200 = 750, and order 6 contributes nothing. The skipping also changes the average, because the NULL row is left out of the divisor too.

Let's run the three functions side by side. The snippets use Python's built-in sqlite3 module so you can execute them anywhere. The SQL inside is ordinary SQL.

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE orders (id INT, customer TEXT, amount INT, status TEXT)')
db.executemany('INSERT INTO orders VALUES (?, ?, ?, ?)', [
    (1, 'Asha', 100, 'pending'),
    (2, 'Ben', 250, 'paid'),
    (3, 'Asha', 150, 'paid'),
    (4, 'Chen', 50, 'cancelled'),
    (5, 'Dev', 200, 'paid'),
    (6, 'Ben', None, 'pending'),
])

q = 'SELECT COUNT(*), COUNT(amount), SUM(amount), AVG(amount) FROM orders'
print(db.execute(q).fetchone())

Rows, non-NULL values, total and mean

Six rows, five real amounts, a total of 750, and a mean of 150. A WHERE clause runs before the aggregate, so it decides which rows the function sees. SELECT SUM(amount) FROM orders WHERE status = 'paid'; adds only orders 2, 3 and 5. That is 250 + 150 + 200 = 600.

The result column gets an unhelpful default name such as SUM(amount). Use AS to give it a readable one, for example SELECT AVG(amount) AS avg_order FROM orders;. The alias becomes the column header, which also makes it easier to read from application code.

q = "SELECT SUM(amount) AS paid_total FROM orders WHERE status = 'paid'"
cur = db.execute(q)
print(cur.description[0][0], cur.fetchone()[0])

A filtered sum with a named result

If a column holds integers, some databases do the averaging in integer arithmetic and may truncate the result. For example, 7 / 2 can give 3 instead of 3.5. Others return a decimal automatically. The behavior depends on the database, so cast when in doubt. Turn one side into a decimal type before dividing or averaging.

print(db.execute('SELECT 7 / 2, 7 / 2.0, CAST(7 AS REAL) / 2').fetchone())

Integer division versus a cast

The first value is truncated to 3, while the two decimal forms give 3.5. For a column the idea is the same, for example AVG(CAST(amount AS DECIMAL(10,2))) or AVG(amount * 1.0).

If no rows match, COUNT returns 0. SUM returns NULL, not 0, because there is nothing to add and the total is undefined. A report that shows a blank where you expected zero is the usual symptom. Wrap the sum with COALESCE(SUM(amount), 0) to supply a default.

q = "SELECT SUM(amount), COALESCE(SUM(amount), 0) FROM orders WHERE status = 'refunded'"
print(db.execute(q).fetchone())

No refunded orders exist

Putting DISTINCT inside the parentheses makes the function look at each different value only once. COUNT(DISTINCT customer) counts people rather than orders. There are 6 orders but only 4 customers, because Asha and Ben each ordered twice. SUM(DISTINCT amount) adds each different amount once, so repeated values are ignored.

print(db.execute('SELECT COUNT(DISTINCT customer), COUNT(customer) FROM orders').fetchone())
q = 'SELECT SUM(x), SUM(DISTINCT x) FROM (SELECT 100 AS x UNION ALL SELECT 100 UNION ALL SELECT 200)'
print(db.execute(q).fetchone())

Distinct customers, and a sum over repeated values

In the second query the values are 100, 100 and 200. The plain sum is 400, while the distinct sum counts the repeated 100 once and gives 300. The orders table has no repeated amounts, so its SUM(DISTINCT amount) still equals 750.

MIN and MAX are the two aggregates that pick a single value out of a column instead of combining all of them. MIN(col) returns the smallest value, MAX(col) returns the largest, and both quietly skip every NULL on the way. Only the values that actually exist take part in the comparison.

On the sample orders table, the amount column holds values from 50 up to 250, plus one order whose amount is NULL. The NULL is ignored, so the smallest amount is 50 and the largest is 250. A NULL never counts as the smallest, even though it might feel like 'nothing' should be less than everything.

The example below rebuilds a small table with the same shape as orders (id, customer, city, amount, status), including one NULL amount, in an in-memory SQLite database. The later examples in this section reuse the same db.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER, customer TEXT, city TEXT, amount INTEGER, status TEXT)")
db.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", [
    (1, "Asha", "Pune", 120, "paid"),
    (2, "Ravi", "Delhi", 250, "paid"),
    (3, "Asha", "Pune", 50, "refunded"),
    (4, "Meera", "Mumbai", None, "pending"),
    (5, "Kiran", "Delhi", 180, "paid"),
])

row = db.execute("SELECT MIN(amount), MAX(amount), MIN(customer), MAX(customer) FROM orders").fetchone()
print(row)

One row comes back, holding four single values.

MIN and MAX are not limited to numbers. They work on any type that can be put in order. For text, the order is alphabetical, so MIN(customer) is 'Asha' and MAX(customer) is whichever name sorts last. For dates, the smallest value is the earliest date and the largest is the latest one.

The sample orders table has no order_date column, so the date example below uses a separate, hypothetical table called dated that holds one. Dates stored as ISO text such as 2026-03-20 sort correctly as plain text, which is a good reason to store them that way.

There is one more rule to know. If there is nothing to compare, MIN and MAX have no value to return, so they return NULL. This happens when every value in the column is NULL, and also when the filter leaves no rows at all. It does not raise an error and it does not return 0.

db.execute("CREATE TABLE dated (order_date TEXT)")
db.executemany("INSERT INTO dated VALUES (?)", [("2026-01-05",), ("2026-03-20",), ("2026-02-11",)])

print(db.execute("SELECT MIN(order_date), MAX(order_date) FROM dated").fetchone())
print(db.execute("SELECT MAX(amount) FROM orders WHERE status = 'shipped'").fetchone())

No order has the status 'shipped', so the second query finds no rows.

MAX only tells you the largest value. It does not tell you which order that value came from. If you want the whole row with the largest amount (the customer, city and status too), MAX alone cannot do it. You have two reliable tools, shown in the chart below.

The sort pattern orders the rows from largest to smallest and keeps the first. It must also drop the NULL amounts with WHERE amount IS NOT NULL. Databases disagree on where NULLs land in a sort: PostgreSQL and Oracle put them first under DESC, so without the filter the NULL order could win. You can also write NULLS LAST, where the database supports it. The subquery pattern finds the maximum first, then selects every row that matches it, so tied orders all come back.

The tempting shortcut is SELECT customer, MAX(amount) FROM orders;. It mixes one value per row (customer) with one value for the whole table (MAX(amount)). Strict databases such as PostgreSQL and SQL Server reject it with an error. MySQL's lenient mode accepts it and may fill customer from an arbitrary row, which can be the wrong person.

SQLite is a special case. With a single bare MAX() or MIN(), it takes the other columns from the row that holds the extreme. That is a SQLite-only extension and does not carry over to other databases, so do not rely on it. If you want a maximum for each customer, say so with GROUP BY.

print(db.execute("SELECT customer, MAX(amount) FROM orders").fetchone())
print(db.execute("SELECT customer, MAX(amount) FROM orders GROUP BY customer ORDER BY customer").fetchall())

The first line runs only because SQLite allows it. The second works everywhere.

COUNT looks like one function, but it answers two different questions depending on what you put in the brackets. COUNT(*) asks how many rows are there. COUNT(col) asks how many rows have a real value in this column. On a table with no NULLs the two agree, so the difference only shows up when some values are missing.

The sample orders table below has 6 rows. Meena's amount is NULL, and Kiran's city is NULL. The program builds this table in memory and runs the first set of counts, so you can see the numbers come from real queries.

import sqlite3

con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE orders (id INTEGER, customer TEXT, city TEXT, amount INTEGER)')
con.executemany('INSERT INTO orders VALUES (?, ?, ?, ?)', [
    (1, 'Asha',  'Hyderabad', 500),
    (2, 'Ravi',  'Pune',      300),
    (3, 'Asha',  'Hyderabad', 700),
    (4, 'Meena', 'Hyderabad', None),
    (5, 'Kiran', None,        450),
    (6, 'Ravi',  'Pune',      250),
])

def one(sql):
    return con.execute(sql).fetchone()[0]

print('COUNT(*)              =', one('SELECT COUNT(*) FROM orders'))
print('COUNT(amount)         =', one('SELECT COUNT(amount) FROM orders'))
print('COUNT(city)           =', one('SELECT COUNT(city) FROM orders'))
print('COUNT(DISTINCT customer) =', one('SELECT COUNT(DISTINCT customer) FROM orders'))

Sample orders table, with one NULL amount and one NULL city

COUNT(*) does not inspect any column. It simply counts rows, so a row full of NULLs still counts as one. That is why it returns 6 here, even though two of the rows have a gap in them.

COUNT(amount) looks at the amount value of each row and counts it only when the value is not NULL. Meena's order has no amount, so it is left out and the answer is 5.

The same rule applies to city. Five rows have a city filled in, but Kiran's row has NULL there, so it is not counted. That is the reason COUNT(city) is 5 and not 6. Nothing is wrong with the data; the function is only counting known values, and Kiran's city is unknown.

Asha and Ravi each placed two orders, so six rows belong to only four people. Adding DISTINCT makes SQL ignore repeated values before counting, which gives 4: Asha, Ravi, Meena and Kiran. DISTINCT skips NULLs too, so it counts different known values only.

COUNT(1) hands SQL a constant for every row. A constant is never NULL, so every row is counted, and the result always matches COUNT(*). Neither form looks at your column values, so pick COUNT(*) because it says what you mean.

Because COUNT(*) counts all rows and COUNT(col) counts only the filled ones, the gap between them is the number of missing values: NULLs in a column = COUNT(*) - COUNT(col). The next program runs this subtraction, along with COUNT(1), COUNT(NULL) and COUNT(DISTINCT city). It reuses the one helper from the first program.

Here are the four forms from the sample table next to each other. Notice how the answer changes with what you ask SQL to look at.

When you are unsure which form to use, walk through this small decision. It comes down to what you want the number to mean.

A NULL in a column does not mean zero or empty. It means the value is unknown. Aggregate functions respect that: SUM, AVG, MIN, MAX and COUNT(col) all ignore NULL inputs. They only work with the values they actually know. The one exception is COUNT(*), which counts rows and never looks at any column.

To see this, we use a small orders table with six rows. One order has a NULL amount, and one order has a NULL city. The five known amounts add up to 750.

Because the NULL amount is skipped, AVG(amount) divides 750 by 5 rows, not by 6, and gives 150. If you treated the unknown amount as 0, you would divide 750 by 6 and get 125. When you want that second answer, you have to ask for it with COALESCE, which swaps a NULL for a value you choose.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER, customer TEXT, city TEXT, amount INTEGER)")
db.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)", [
    (1, "Asha", "Pune", 100),
    (2, "Ben", "Delhi", 150),
    (3, "Chen", "Pune", 200),
    (4, "Dia", "Delhi", None),
    (5, "Eli", None, 100),
    (6, "Fay", "Pune", 200),
])

q = """SELECT COUNT(*), COUNT(amount), SUM(amount),
              AVG(amount), AVG(COALESCE(amount, 0))
       FROM orders"""
print(db.execute(q).fetchone())

COUNT(*), COUNT(amount), SUM, AVG skipping NULL, AVG treating NULL as 0

Read the tuple from left to right: six rows, five known amounts, a sum of 750, an average of 150 that skips the NULL, and an average of 125 once the NULL is counted as zero.

NULL also breaks the usual meaning of =. Comparing anything to an unknown value gives an unknown answer, and WHERE only keeps rows where the condition is true. So WHERE amount = NULL matches nothing, even for the row whose amount is NULL. Use IS NULL or IS NOT NULL instead.

What if every input is NULL? Then there is nothing for the aggregate to work with. Keep only the NULL-amount row and run all the aggregates on it. SUM, AVG, MIN and MAX return NULL, because there is no known value to add, average or compare. COUNT(amount) returns 0, because it counts known values and there are none. COUNT(*) still returns 1, because the row exists.

q = """SELECT SUM(amount), AVG(amount), MIN(amount), MAX(amount),
              COUNT(amount), COUNT(*)
       FROM orders WHERE amount IS NULL"""
print(db.execute(q).fetchone())

Python shows SQL NULL as None

WHERE treats two NULLs as different unknowns, but GROUP BY does the opposite. It puts all rows with a NULL grouping value into one group. In our table only Eli's order has a NULL city, so that group has one row. Pune has three rows and Delhi has two.

The NULL city shows up as its own line in the result. It is easy to overlook, and it can mean a missing city is quietly reported as if it were a real place.

The last step is not technical. Skipping a NULL and replacing it with 0 both run without errors, but they answer different business questions. Decide which question you are asking before you write the query.

Plain aggregates squash the whole table into a single answer. Often you want one answer per category instead, such as orders per city or spend per customer. GROUP BY does this. It sorts the rows into buckets, one bucket per distinct value of the column you name. Then it runs the aggregate once inside each bucket.

In this section every example uses the same six-row orders table. It has the columns id, customer, city, amount and order_date. One order has no city at all, so its city is NULL.

Take GROUP BY city. The database reads each row, looks at its city, and drops the row into the bucket for that value. The two Pune orders land together, the three Delhi orders land together, and Kiran's order sits alone in a bucket for NULL. NULLs are not thrown away. All NULLs in the grouping column form one group of their own.

Once the buckets exist, COUNT(*) simply counts the rows inside each one. The query below builds the table in an in-memory SQLite database and runs it. The small show helper prints NULL as the word NULL. Later examples reuse conn and show.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
CREATE TABLE orders (id INTEGER, customer TEXT, city TEXT, amount INTEGER, order_date TEXT);
INSERT INTO orders VALUES
(1, 'Asha',  'Pune',  100, '2024-01-05'),
(2, 'Ravi',  'Delhi', 100, '2024-01-12'),
(3, 'Asha',  'Pune',  150, '2024-02-03'),
(4, 'Meena', 'Delhi',  50, '2024-02-14'),
(5, 'Kiran', NULL,    200, '2024-02-20'),
(6, 'Ravi',  'Delhi', 150, '2024-03-02');
""")

def show(sql):
    for row in conn.execute(sql):
        print("  ".join("NULL" if v is None else str(v) for v in row))

show("SELECT city, COUNT(*) FROM orders GROUP BY city")

Orders per city: SELECT city, COUNT(*) FROM orders GROUP BY city;

The result has three rows: Pune 2, Delhi 3 and NULL 1. Together they account for all six orders. There is one output row per distinct group value, no matter how many input rows fell into each bucket. The order the groups come back in is not something to rely on unless you add ORDER BY.

The same idea works with any aggregate. To see how much each customer has spent, group by customer and use SUM(amount). Asha's two orders (100 and 150) go into one bucket and add up to 250. Ravi's two orders (100 and 150) also give 250. Meena has one order of 50, and Kiran has one of 200.

show("SELECT customer, SUM(amount) FROM orders GROUP BY customer")

Spend per customer: SELECT customer, SUM(amount) FROM orders GROUP BY customer;

There are four customers, so there are four rows. The three Delhi orders do not collapse into one row here, because this query groups by customer, not by city. What you put after GROUP BY decides what a bucket is.

This leads to the main rule of grouping. After grouping, each output row stands for a whole bucket. The database can show you the value you grouped by, because it is the same for every row in the bucket. It can also show you an aggregate computed over the bucket. It cannot show you a plain column that differs from row to row inside the bucket, because it would have to pick one value arbitrarily. Look at the Delhi bucket. It holds the customer values Ravi, Meena and Ravi, so a single customer cell for that bucket makes no sense.

So every column in SELECT that is not inside an aggregate must also appear in GROUP BY. If you really want that extra column, either add it to GROUP BY, which makes smaller buckets, or wrap it in an aggregate such as MAX(customer).

-- Wrong: customer is neither grouped nor aggregated
SELECT city, customer, COUNT(*)
FROM orders
GROUP BY city;

-- Right: group by both columns, or aggregate customer
SELECT city, COUNT(*), COUNT(DISTINCT customer)
FROM orders
GROUP BY city;

Most databases (PostgreSQL, SQL Server, Oracle) reject the first query with an error.

The rows come out of a grouped query in no promised order. To rank the groups, such as showing the biggest spenders first, add an ORDER BY on the aggregate. You can sort by SUM(amount) directly. It does not have to appear as a column in SELECT, although it usually does. Asha and Ravi tie at 250, so a second sort key keeps the result stable.

show("""
SELECT customer, SUM(amount)
FROM orders
GROUP BY customer
ORDER BY SUM(amount) DESC, customer
""")

Biggest spenders first

This works because ORDER BY runs after the grouping. By then every bucket has already been summed, so there is a total to sort on. The reverse would be impossible. Before the buckets exist, there is no per-customer total to compare.

This is one case of a wider fact. You write a query in the order SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. The database does not run it in that order. Its logical order is below.

Reading the chain from the top: the database first gets the rows from FROM. WHERE removes rows you do not want, before any bucket exists. GROUP BY then makes the buckets, and HAVING can remove entire buckets by their aggregate value. Only then does SELECT build the output columns, and ORDER BY sorts what is left. HAVING gets its own section later, so for now just note where it sits.

GROUP BY does not have to name a bare column. It accepts any expression, and rows whose expression gives the same value share a bucket. This helps when the stored values are almost the same but not identical. Here a signups table holds the city typed in three different ways.

conn.executescript("""
CREATE TABLE signups (city TEXT);
INSERT INTO signups VALUES ('Pune'), ('pune'), ('PUNE'), ('Delhi');
""")

show("SELECT LOWER(city), COUNT(*) FROM signups GROUP BY LOWER(city)")

Fold the spellings together with LOWER

Grouping by plain city would have given four buckets, because Pune, pune and PUNE are different strings. LOWER(city) makes them identical before the buckets are formed, so they merge into one group of three. Notice that SELECT repeats the same expression. The rule from earlier still applies: what you select must match what you group by.

Dates are the other classic case. An order_date is nearly unique, so grouping by it directly gives almost one bucket per order. To report by month, group by the part of the date you care about. In SQLite that is strftime('%Y-%m', order_date). Other databases use DATE_TRUNC, EXTRACT or MONTH(), but the idea is the same.

show("""
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*), SUM(amount)
FROM orders
GROUP BY strftime('%Y-%m', order_date)
""")

Orders and revenue per month

Grouping by one column puts rows together when they share that one value. When you list two columns, SQL asks for more: two rows land in the same group only if they match on both columns. So you get one group per distinct combination of values, not one group per column.

The examples on this page use the six-row orders table, trimmed to the columns that matter here. Notice the order with no city (a NULL) and the pending order with no amount yet.

Delhi has two paid orders, so they share one combination. Delhi/pending is a different combination, even though the city matches. Here is the process the database follows:

Run it and the output has one row per combination. Because we add ORDER BY city, status, the rows come out sorted instead of in whatever order the database happened to build them.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER, city TEXT, status TEXT, amount INTEGER)")
db.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)", [
    (1, 'Pune', 'paid', 500),
    (2, 'Pune', 'refunded', 300),
    (3, 'Delhi', 'paid', 400),
    (4, 'Delhi', 'paid', 250),
    (5, 'Delhi', 'pending', None),
    (6, None, 'paid', 150),
])

def show(sql):
    print(sql)
    for row in db.execute(sql):
        print(" | ".join("NULL" if v is None else str(v) for v in row))

show("SELECT city, status, COUNT(*) FROM orders GROUP BY city, status ORDER BY city, status")

Five combinations from six rows

So the result is Pune/paid 1, Pune/refunded 1, Delhi/paid 2, Delhi/pending 1 and NULL/paid 1. The missing city forms its own group: GROUP BY treats all NULLs as one value, so the NULL/paid row is a real group and not something dropped.

GROUP BY city, status and GROUP BY status, city produce exactly the same groups, because a combination of two values is the same pair whichever you name first. Order does not change what is grouped. It only affects how you may read the result, since databases often hand back rows sorted by the first grouping column. If you care about the order, say so with ORDER BY rather than relying on that habit.

Every column you add splits the existing groups into finer pieces. The number of rows can only grow or stay the same, and it can never exceed the row count of the table, which is reached when every row has a unique combination.

Any aggregate works per combination. With SUM(amount), Delhi/paid adds 400 and 250. Delhi/pending has a single row whose amount is NULL, and SUM ignores NULLs, so a group with nothing to add up returns NULL, not 0.

show("SELECT city, status, SUM(amount) FROM orders GROUP BY city, status ORDER BY city, status")
print()
show("SELECT city, status, COUNT(*) FROM orders GROUP BY status, city ORDER BY city, status")
print()
show("SELECT SUM(n) FROM (SELECT COUNT(*) AS n FROM orders GROUP BY city, status)")

Sums, a swapped column order, and the total of the counts

The middle query swaps the grouping columns and returns the same five rows with the same counts as before. The last query adds up the count column, and it gives 6, the number of rows in the table. Every row belongs to exactly one group, so the group counts always add up to the total.

Each output row stands for a whole group, so any plain column in SELECT must have one value per group. The rule is simple: every non-aggregated column in SELECT must also appear in GROUP BY. Aggregates like COUNT and SUM are exempt because they collapse the group into one number.

Suppose you start with a per-city count and then decide you want status as well. Selecting the extra column without grouping by it is an error in most databases, because a city with several statuses has no single status to show.

-- fails: status is neither grouped nor aggregated
SELECT city, status, COUNT(*) FROM orders GROUP BY city;

-- works: status joins the grouping
SELECT city, status, COUNT(*) FROM orders GROUP BY city, status;

Adding a column to SELECT means adding it to GROUP BY

Finish with ORDER BY city, status so the grouped rows come out in a predictable, easy-to-scan order, with each city's statuses sitting together. ORDER BY runs after grouping, so it can sort by the grouping columns or by an aggregate such as the count.

SQL gives you two ways to throw data away, and the difference is when each one runs. WHERE looks at individual rows before any grouping happens. HAVING looks at whole groups after the aggregates have been calculated. Once you see that order of events, you rarely mix them up.

The examples in this section use a small version of the orders table, four rows in two cities. Delhi's orders add up to 300 and Pune's add up to 250, and one Pune order was cancelled.

The first program builds this table in memory and defines a small show helper that prints each result row. Then it keeps only the cities whose total is greater than 300 and compares that with at least 300.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript('''
CREATE TABLE orders (id INTEGER, city TEXT, status TEXT, amount INTEGER);
INSERT INTO orders VALUES
  (1, 'Delhi', 'paid', 100), (2, 'Delhi', 'paid', 200),
  (3, 'Pune', 'paid', 150), (4, 'Pune', 'cancelled', 100);
''')

def show(sql):
    rows = db.execute(sql).fetchall()
    if not rows:
        print("(no rows)")
    for r in rows:
        print(*r, sep=" | ")

base = "SELECT city, SUM(amount) FROM orders GROUP BY city "
print("> 300")
show(base + "HAVING SUM(amount) > 300")
print(">= 300")
show(base + "HAVING SUM(amount) >= 300")

HAVING runs after SUM has been worked out for each city

Delhi totals exactly 300, and 300 is not greater than 300, so the strict test drops it. Pune totals 250 and fails both tests. Changing > to >= is all it takes to keep Delhi.

Because WHERE runs before groups exist, there is no total or count for it to look at yet. Writing WHERE SUM(amount) > 300 asks the database to use a number it has not calculated, so it refuses with an error. Anything that depends on an aggregate has to go in HAVING.

The two clauses work well together. WHERE first removes the rows you never wanted to count, and HAVING then judges the groups built from what remains. The program below runs the same city count three ways, reusing show from the previous example.

try:
    show("SELECT city FROM orders WHERE SUM(amount) > 300 GROUP BY city")
except sqlite3.OperationalError:
    print("WHERE with SUM: error")

count = "SELECT city, COUNT(*) FROM orders "
print("HAVING only")
show(count + "GROUP BY city HAVING COUNT(*) >= 2 ORDER BY city")
print("WHERE + HAVING")
show(count + "WHERE status = 'paid' GROUP BY city "
     "HAVING COUNT(*) >= 2 ORDER BY city")

The status filter changes which rows get counted

With HAVING alone, Pune has two rows and passes. Adding WHERE status = 'paid' removes the cancelled order first, so Pune is left with one row and fails COUNT(*) >= 2. Only Delhi survives. The row filter changed what was counted, and the group filter then judged the counts.

Since WHERE runs first, it shrinks the input before any grouping work starts. Fewer rows to bucket and add up means less work for the database, so filtering early is generally faster than building every group and discarding most of them afterwards.

That leads to a habit worth keeping: if a condition only needs a single row's own columns, put it in WHERE. HAVING will accept a plain column that appears in GROUP BY, so the query below works, but it groups every city first and only then throws Pune's rivals away.

-- works, but groups every city before filtering
SELECT city, SUM(amount) FROM orders
GROUP BY city HAVING city = 'Pune';

-- better: filter rows first, group fewer of them
SELECT city, SUM(amount) FROM orders
WHERE city = 'Pune' GROUP BY city;

Both return Pune with 250, but the second does less work

Here is the whole comparison in one place.

One last case: HAVING does not strictly need a GROUP BY. If you leave GROUP BY out, the database treats the whole table as a single group, so the aggregate covers every row and HAVING either keeps that one result or returns nothing. Some database versions are stricter about this, so check yours before relying on it.

SELECT SUM(amount) FROM orders
HAVING SUM(amount) > 500;

The total is 550, so the single group passes

Change the test to > 600 and the same query returns no rows at all, because the only group failed.

So far every query read from one table. Real questions usually need two, for example "how much did each customer tier spend?" The order of work matters here: the database builds the joined rows first, and only then does GROUP BY sort them into groups. Every aggregate therefore sees the joined rows, not the original tables.

To try this, we add a customers(id, name, tier) table next to orders. Orders store the customer's name, so the join condition is c.name = o.customer. The setup below also creates a small items table that we need later, and a show helper that prints each result row on one line. Each example here uses its own few rows, so the numbers are small enough to check by hand.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE orders(id INTEGER, customer TEXT, amount INTEGER);
INSERT INTO orders VALUES (1,'Asha',100),(2,'Ben',50),(3,'Asha',70),(4,'Chen',200);
CREATE TABLE customers(id INTEGER, name TEXT, tier TEXT);
INSERT INTO customers VALUES (1,'Asha','gold'),(2,'Ben','silver'),(3,'Chen','gold'),(4,'Dev','silver');
CREATE TABLE items(order_id INTEGER, product TEXT);
INSERT INTO items VALUES (1,'pen'),(1,'ink'),(1,'pad'),(2,'cap'),(4,'bag'),(4,'tag');
''')

def show(sql, con=db):
    for row in con.execute(sql):
        print(' | '.join(str(v) for v in row))

show('''
SELECT c.tier, SUM(o.amount)
FROM orders o JOIN customers c ON c.name = o.customer
GROUP BY c.tier ORDER BY c.tier
''')

Total spend per customer tier

Gold is Asha's 100 and 70 plus Chen's 200, which makes 370. Silver is just Ben's 50. Dev is silver too but has no orders, so no row of Dev's ever reached the groups. That detail comes back on the next page.

A join can return more rows than the table you started from. If one order matches three rows in items, the join produces three rows for that order, and each copy carries the full order amount. This is called fan-out. A SUM over the joined rows then adds the same amount several times.

print('orders only')
show('SELECT SUM(amount) FROM orders')
print('after joining items')
show('''
SELECT SUM(o.amount)
FROM orders o JOIN items i ON i.order_id = o.id
''')

Same orders, two very different totals

The real total is 420. Order 1 has three items, so its 100 counted three times. Order 4 has two items, so its 200 counted twice. Order 3 has no items, so the inner join dropped it entirely. The query raised no error and the number looks plausible, which is what makes fan-out dangerous.

The fix is to aggregate the many side first, in a subquery or a CTE, so it collapses to one row per order. Then join that small result. Each order now appears once, and its amount can no longer be repeated.

show('''
WITH item_counts AS (
  SELECT order_id, COUNT(*) AS n_items
  FROM items GROUP BY order_id
)
SELECT o.customer, SUM(o.amount), SUM(t.n_items)
FROM orders o LEFT JOIN item_counts t ON t.order_id = o.id
GROUP BY o.customer ORDER BY o.customer
''')

Collapse items to one row per order, then join

The amounts are right again: Asha is 100 + 70 = 170. We used a LEFT JOIN so order 3, which has no items, is still counted. The item total for Asha is 3 because SUM skips the NULL that order 3 contributes.

A LEFT JOIN keeps every row from the left table, even with no match on the right. For customers left-joined to orders, Dev still appears, with NULL in every order column. Here the two kinds of count split apart. COUNT(*) counts rows, and Dev's single NULL-filled row is still a row. COUNT(o.id) counts only non-NULL values, so it correctly gives 0.

show('''
SELECT c.name, COUNT(*), COUNT(o.id)
FROM customers c LEFT JOIN orders o ON o.customer = c.name
GROUP BY c.id ORDER BY c.id
''')

Dev has no orders

Fan-out also affects counts. Join customers to orders and then to items, and each order is repeated once per item. COUNT(o.id) now counts those repeats. COUNT(DISTINCT o.id) counts each order only once, however many times it was repeated.

show('''
SELECT c.name, COUNT(o.id), COUNT(DISTINCT o.id)
FROM customers c
LEFT JOIN orders o ON o.customer = c.name
LEFT JOIN items i ON i.order_id = o.id
GROUP BY c.id ORDER BY c.id
''')

Plain COUNT inflates, DISTINCT does not

Asha has two orders but four joined rows: order 1 appears three times, and order 3 once with a NULL item. The DISTINCT count is the true number of orders.

Names are not unique, so grouping by c.name can merge two different people into one group. Group by the key, c.id, which identifies one customer. Add c.name to the GROUP BY as well if you want to show it. In the example below, two customers are both called Sam.

db2 = sqlite3.connect(':memory:')
db2.executescript('''
CREATE TABLE customers(id INTEGER, name TEXT);
INSERT INTO customers VALUES (1,'Sam'),(2,'Sam'),(3,'Ria');
CREATE TABLE orders(id INTEGER, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (1,1,40),(2,2,60),(3,3,25);
''')

base = 'FROM customers c JOIN orders o ON o.customer_id = c.id '
print('by name')
show('SELECT c.name, SUM(o.amount) ' + base + 'GROUP BY c.name ORDER BY c.name', db2)
print('by id')
show('SELECT c.name, SUM(o.amount) ' + base + 'GROUP BY c.id, c.name ORDER BY c.id', db2)

Two different Sams

Grouping by name reports one Sam with 100, which is two people's money added together. Grouping by id keeps them apart.

The last trap is where a filter on the right-hand table goes. A condition in WHERE runs after the join. Dev's row has a NULL amount, and NULL fails o.amount > 60, so the row is thrown away. That turns your LEFT JOIN into an inner join. Put the condition in ON and it only decides which orders match, so customers without a match still survive.

print('where')
show('''
SELECT c.name, COUNT(o.id)
FROM customers c LEFT JOIN orders o ON o.customer = c.name
WHERE o.amount > 60
GROUP BY c.id ORDER BY c.id
''')
print('on')
show('''
SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer = c.name AND o.amount > 60
GROUP BY c.id ORDER BY c.id
''')

Count each customer's orders over 60

Most aggregation bugs do not crash. They return a number that looks reasonable and is wrong. This section walks through eight of them, using one small orders table. Two of them (a stray column and an aggregate in WHERE) often fail loudly. The rest quietly give you a plausible wrong answer.

The table has six rows. Two of them are pending orders with no amount yet, so amount is NULL. Every later example reuses the connection and the show helper defined here.

import sqlite3
db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE orders (id INTEGER, customer TEXT, category TEXT, amount INTEGER, status TEXT);
INSERT INTO orders VALUES
  (1, 'Asha', 'books', 20, 'paid'),
  (2, 'Asha', 'books', 30, 'paid'),
  (3, 'Ben',  'games', 50, 'paid'),
  (4, 'Ben',  'games', NULL, 'pending'),
  (5, 'Cara', 'books', 40, 'paid'),
  (6, 'Cara', 'games', NULL, 'pending');
''')

def show(sql):
    for row in db.execute(sql):
        print(row)

show('SELECT COUNT(*) FROM orders')

Setup used by every example below

After GROUP BY customer, each output row stands for a whole group of input rows. Asking for category as well is ambiguous, because Asha's group might hold several categories and the row can only show one. PostgreSQL and MySQL in its strict mode reject the query with an error. Lenient modes, and SQLite, do not complain. They pick a value from some row in the group and show it, and you cannot tell which one.

-- Error in PostgreSQL and strict MySQL; arbitrary category elsewhere
SELECT customer, category, COUNT(*)
FROM orders
GROUP BY customer;

-- Fix: group by both, or aggregate the column
SELECT customer, category, COUNT(*)
FROM orders
GROUP BY customer, category;

Every selected column must be grouped or wrapped in an aggregate

WHERE filters individual rows before any grouping happens, so no group totals exist yet. A condition like COUNT(*) > 1 has nothing to count at that stage, and the database rejects it. Conditions on aggregate results belong in HAVING, which runs after the groups are built.

-- Error: aggregate functions are not allowed in WHERE
SELECT customer, COUNT(*)
FROM orders
WHERE COUNT(*) > 1
GROUP BY customer;

-- Fix: filter rows with WHERE, filter groups with HAVING
SELECT customer, COUNT(*)
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING COUNT(*) > 1;

Row filters in WHERE, group filters in HAVING

Three of the mistakes come from the same fact: aggregate functions skip NULL values. That is usually what you want, but it surprises people when they expect a NULL to behave like a zero or like a real row. Compare what each function does with our two NULL amounts.

COUNT(*) counts rows, so it includes the two pending orders. If you meant the number of orders that have an amount, you need COUNT(amount). AVG behaves the same way. It adds up the four known amounts (140) and divides by 4, giving 35. Many people expect the two NULLs to count as zero, which would divide 140 by 6 and give about 23.33. The plain AVG is therefore larger than that expectation. Neither number is wrong, but you must pick the one that matches your question and say so with COALESCE when you want zeros.

When no rows match, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL. A report that shows total revenue for refunded orders will display a blank instead of 0. Wrap the aggregate in COALESCE(..., 0) when the output is meant for people or for arithmetic.

show('''SELECT COUNT(*), COUNT(amount),
       ROUND(AVG(amount), 2), ROUND(AVG(COALESCE(amount, 0)), 2)
FROM orders''')

show('''SELECT SUM(amount), COALESCE(SUM(amount), 0)
FROM orders WHERE status = 'refunded' ''')

Row count vs value count, two averages, and an empty sum

The SELECT list is evaluated after GROUP BY in standard SQL, so an alias defined there does not officially exist yet. MySQL, PostgreSQL and SQLite let you use it in GROUP BY anyway, but SQL Server and Oracle do not. If the query has to run on more than one database, repeat the expression or group by its position in the select list.

-- Works in some databases only
SELECT UPPER(category) AS cat, COUNT(*)
FROM orders
GROUP BY cat;

-- Portable: repeat the expression
SELECT UPPER(category) AS cat, COUNT(*)
FROM orders
GROUP BY UPPER(category);

-- Portable: group by position
SELECT UPPER(category) AS cat, COUNT(*)
FROM orders
GROUP BY 1;

Three ways to write the same grouping

Joining orders to a table with several rows per order repeats the order row once for each match. Add up amount afterwards and each order is counted that many times. Suppose order 1 has two items and order 3 has three. The amounts 20 and 50 become 20+20 and 50+50+50, so revenue is overstated.

The cure is to aggregate each many-side table down to one row per order before joining, or to sum from the table that holds the amount without joining at all. Then every order contributes exactly once.

db.executescript('''
CREATE TABLE items (order_id INTEGER, sku TEXT);
INSERT INTO items VALUES
  (1, 'pen'), (1, 'ink'),
  (3, 'disc'), (3, 'case'), (3, 'cable');
''')

show('''SELECT SUM(o.amount)
FROM orders o JOIN items i ON i.order_id = o.id''')

show('''SELECT SUM(o.amount)
FROM orders o
JOIN (SELECT order_id, COUNT(*) AS n
      FROM items GROUP BY order_id) i
  ON i.order_id = o.id''')

Inflated total, then the total after collapsing items first

It is tempting to compute an average per category and then average those results to get an overall figure. This only works when every group has the same number of values. Here books has three amounts (20, 30, 40, average 30) and games has one (50, average 50, since the NULL is skipped). Averaging 30 and 50 gives 40, but the overall average of the four values is 35. The small group pulled the result too hard because the average of averages gives each group an equal vote instead of weighting by size.

show('''SELECT AVG(a)
FROM (SELECT AVG(amount) AS a FROM orders GROUP BY category)''')

show('SELECT AVG(amount) FROM orders')

Average of group averages vs the real overall average

To combine groups correctly, carry the sum and the count from each group and divide the totals, or simply run AVG on the original rows. Never feed averages into another average.

Everything in this chapter hangs off one small orders table. It has six rows, and one of them, order 6, has no amount yet because it is still pending. That single NULL is what makes the aggregate functions behave differently from each other.

There are three ways to count. They differ only in what they are willing to skip, so choose the one that matches the question you are asking.

The four number-crunching functions SUM, AVG, MIN and MAX all skip NULLs. They work only on the values that exist. If there are no values at all, because the table is empty or your WHERE removed every row, they return NULL rather than 0. COUNT is the odd one out: on empty input it returns 0.

The program below builds the table in an in-memory SQLite database and asks for every summary in one query. Later examples reuse the db connection from here.

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE orders (id INTEGER, customer TEXT, status TEXT, amount INTEGER)')
db.executemany('INSERT INTO orders VALUES (?, ?, ?, ?)', [
    (1, 'Asha', 'paid', 100),
    (2, 'Asha', 'paid', 250),
    (3, 'Ben', 'paid', 50),
    (4, 'Ben', 'refunded', 200),
    (5, 'Cara', 'paid', 150),
    (6, 'Cara', 'pending', None),
])
row = db.execute(
    'SELECT COUNT(*), COUNT(amount), COUNT(DISTINCT customer),'
    ' SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM orders'
).fetchone()
print(row)

COUNT(*), COUNT(amount), COUNT(DISTINCT customer), SUM, AVG, MIN, MAX

You write a query top to bottom in one fixed order. The database does not run it in that order, and that gap explains most confusing aggregate errors. The two orders are laid side by side below.

Read the right-hand column as a pipeline. Rows come in from the table, get filtered one by one, get bundled into groups, get filtered again as groups, and only then does SELECT compute the columns you asked for.

This order is why WHERE filters rows and HAVING filters groups. When WHERE runs, no groups exist yet, so it cannot see a SUM. By the time HAVING runs, the groups are built and their totals are known.

Here both filters work together. WHERE keeps only paid orders, GROUP BY makes one group per customer, and HAVING drops any customer whose paid total is 100 or less. Ben falls out, because his only paid order is 50.

rows = db.execute(
    "SELECT customer, COUNT(*), SUM(amount) FROM orders"
    " WHERE status = 'paid'"
    " GROUP BY customer"
    " HAVING SUM(amount) > 100"
    " ORDER BY customer"
).fetchall()
for r in rows:
    print(r)

WHERE trims rows first, HAVING trims groups after

One more rule comes from the same logic. Once you group, each output row stands for a whole bundle, so every column in SELECT must either be wrapped in an aggregate or appear in GROUP BY. A bare column that is neither has no single value to show.

Three habits keep aggregate queries honest when NULLs, empty results and joins get involved. Each one guards against a specific way the numbers go quietly wrong.

First, empty input. If no rows survive the WHERE, SUM returns NULL while COUNT(*) returns 0. Wrapping the sum in COALESCE turns that NULL into a clean 0 that reports and charts can handle.

row = db.execute(
    "SELECT SUM(amount), COUNT(*), COALESCE(SUM(amount), 0)"
    " FROM orders WHERE status = 'cancelled'"
).fetchone()
print(row)

No order is cancelled, so the input is empty

Second, uniqueness. COUNT(*) counts rows, so it answers how many orders there are. COUNT(DISTINCT customer) answers how many different customers placed them. On the sample table those are 6 and 3.

Third, joins. When you join orders to a table that has several rows per order, such as shipments, each order row is repeated once per match. A SUM taken after the join then adds the same amount more than once. The fix is to collapse the many-side to one row per order first, then join. In this example order 1 has two shipments and order 2 has one.

db.execute('CREATE TABLE shipments (order_id INTEGER)')
db.executemany('INSERT INTO shipments VALUES (?)', [(1,), (1,), (2,)])
bad = db.execute(
    'SELECT SUM(o.amount) FROM orders o JOIN shipments s ON s.order_id = o.id'
).fetchone()[0]
good = db.execute(
    'SELECT SUM(o.amount) FROM orders o JOIN ('
    ' SELECT order_id, COUNT(*) AS n FROM shipments GROUP BY order_id'
    ') s ON s.order_id = o.id'
).fetchone()[0]
print(bad, good)

Joining first repeats order 1, pre-aggregating does not

The joined total of 450 counts order 1's 100 twice. After pre-aggregating, the total is the true 350 for the two orders that have shipments.

Work out each answer before you open it. All questions use the same orders table: 6 rows, one NULL amount (id 5) and one NULL city (id 6).

• Aggregates collapse many rows into one value per group, and with no GROUP BY the whole table is a single group.
• COUNT(*) counts rows; COUNT(col) counts known values; all other aggregates skip NULLs, so AVG divides by the non-NULL count.
• Every non-aggregated column in SELECT must also appear in GROUP BY.
• WHERE filters rows before grouping; HAVING filters groups after aggregation, and only HAVING can use aggregates.
• Execution order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, which explains most errors.
• Joins run before grouping, so fan-out inflates sums: pre-aggregate first or count distinct keys.
• Guard empty results with COALESCE(SUM(x), 0) and decide on purpose whether NULL should be ignored or counted as 0.