GROUP BY in SQL: one row per group

The previous lesson summarised a whole table into one row. Usually you want one summary per something: orders per city, revenue per month, students per course. GROUP BY does that. It puts the rows into groups that share the same values, runs the aggregates once for each group, and returns one row per group.

Every query below runs in the browser’s SQLite, version 3.49.1 through sql.js 1.14.2. The last part of the lesson shows how PostgreSQL and MySQL treat the same queries.

One row per city

-- Seven orders; the last one was collected from the shop, so it has no city (NULL).
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Asha',     'Pune',   45000),
  (2, 'Vikram',   'Kochi',  12000),
  (3, 'Asha',     'Pune',   30000),
  (4, 'Meera',    'Jaipur',  8000),
  (5, 'Tenzin',   'Pune',   99000),
  (6, 'Vikram',   'Kochi',  15000),
  (7, 'Gurpreet', NULL,     20000);

-- One output row per city: the rows of each city are counted and added up.
SELECT city,
       count(*)                  AS orders,
       sum(amount_paise) / 100.0 AS revenue_rupees
FROM orders
GROUP BY city
ORDER BY orders DESC, city;

┌────────┬────────┬────────────────┐
│  city  │ orders │ revenue_rupees │
├────────┼────────┼────────────────┤
│ Pune   │ 3      │ 1740.0         │
│ Kochi  │ 2      │ 270.0          │
│        │ 1      │ 200.0          │
│ Jaipur │ 1      │ 80.0           │
└────────┴────────┴────────────────┘

Seven rows went in and four came out, one for each different value of city:

• The three Pune orders became one row whose count(*) is 3 and whose sum is their three amounts added up.
• The order with no city did not vanish. All rows whose city is NULL form one group of their own, shown with an empty cell. Grouping treats NULLs as equal to each other, unlike = in a WHERE clause.
• ORDER BY sorts the groups. Without it the groups come back in whatever order the engine finds convenient, which can change from one version or one plan to the next, so always sort a grouped result you show to someone.

Several columns and expressions

GROUP BY takes a list. Each different combination of the listed values is a group, so grouping by month and category gives one row for every month-and-category pair that occurs in the data:

-- One row per product on an order. placed_on is an ISO date stored as text.
CREATE TABLE order_lines (
  order_id    INTEGER,
  placed_on   TEXT,
  category    TEXT,
  product     TEXT,
  qty         INTEGER,
  price_paise INTEGER
);
INSERT INTO order_lines VALUES
  (101, '2026-01-04', 'Staples', 'Basmati rice 5 kg', 1, 64000),
  (101, '2026-01-04', 'Dairy',   'Paneer 200 g',      2,  9000),
  (102, '2026-01-19', 'Snacks',  'Banana chips',      4,  6000),
  (103, '2026-01-27', 'Staples', 'Toor dal 1 kg',     2, 16500),
  (104, '2026-02-02', 'Dairy',   'Curd 500 g',        2,  4500),
  (104, '2026-02-02', 'Staples', 'Atta 5 kg',         1, 28000),
  (105, '2026-02-14', 'Snacks',  'Bhujia 400 g',      1, 11000),
  (105, '2026-02-14', 'Dairy',   'Paneer 200 g',      1,  9000);

-- Group by an expression (the month) and a column: one row per month and category.
SELECT substr(placed_on, 1, 7)         AS month,
       category,
       sum(qty * price_paise) / 100.0  AS revenue_rupees
FROM order_lines
GROUP BY month, category
ORDER BY month, revenue_rupees DESC, category;

┌─────────┬──────────┬────────────────┐
│  month  │ category │ revenue_rupees │
├─────────┼──────────┼────────────────┤
│ 2026-01 │ Staples  │ 970.0          │
│ 2026-01 │ Snacks   │ 240.0          │
│ 2026-01 │ Dairy    │ 180.0          │
│ 2026-02 │ Staples  │ 280.0          │
│ 2026-02 │ Dairy    │ 180.0          │
│ 2026-02 │ Snacks   │ 110.0          │
└─────────┴──────────┴────────────────┘

The month is not a column of the table: substr(placed_on, 1, 7) cuts 2026-01 out of 2026-01-04. You can group by any expression, and refer to it in three ways:

• by repeating the expression, GROUP BY substr(placed_on, 1, 7), category;
• by the alias given in the SELECT list, GROUP BY month, category, as this query does;
• by position, GROUP BY 1, 2 for the first and second output columns.

All three engines accept all three forms. Grouping by an output alias goes beyond the SQL standard, which groups by input columns only, and MySQL’s manual calls the positions deprecated, so prefer names. With a real date column instead of text, PostgreSQL and MySQL have their own functions for “the month of this date”; the dates lesson shows them.

The rule: grouped or aggregated

Once rows are grouped, each output row stands for a whole group. A column you select must therefore have one value per group. That holds for the columns you grouped by and for aggregates, which turn the group’s values into one. Anything else is a problem:

CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Asha',   'Pune',   45000),
  (2, 'Vikram', 'Kochi',  12000),
  (3, 'Asha',   'Pune',   30000),
  (4, 'Meera',  'Jaipur',  8000),
  (5, 'Tenzin', 'Pune',   99000),
  (6, 'Vikram', 'Kochi',  15000);

-- customer is neither grouped nor aggregated: Pune has two different customers,
-- so which one should its row show? SQLite answers anyway.
SELECT city, customer, count(*) AS orders
FROM orders
GROUP BY city
ORDER BY city;

┌────────┬──────────┬────────┐
│  city  │ customer │ orders │
├────────┼──────────┼────────┤
│ Jaipur │ Meera    │ 1      │
│ Kochi  │ Vikram   │ 2      │
│ Pune   │ Asha     │ 3      │
└────────┴──────────┴────────┘

Pune has orders from Asha and from Tenzin, yet its row shows Asha. SQLite took the value from one of the group’s rows, and nothing in the query says which one: on other data, another version or another plan it could be Tenzin. SQLite calls such a column a bare column and documents it as its own extension. PostgreSQL and MySQL refuse the query instead:

• PostgreSQL rejects any selected column that is not grouped, not inside an aggregate, and not functionally dependent on the grouped columns.
• MySQL 8.4 rejects it with error 1055, because its default SQL mode includes ONLY_FULL_GROUP_BY.

The error is the more helpful answer. Decide what you meant: if you wanted each customer, group by them too; if you wanted one example customer per city and any of them will do, say so with an aggregate such as min(customer).

The SQLite exception for MIN and MAX

SQLite gives bare columns one useful, documented meaning. When the query has exactly one min() or max(), the bare columns come from the row that holds that minimum or maximum:

CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Asha',   'Pune',   45000),
  (2, 'Vikram', 'Kochi',  12000),
  (3, 'Asha',   'Pune',   30000),
  (4, 'Meera',  'Jaipur',  8000),
  (5, 'Tenzin', 'Pune',   99000),
  (6, 'Vikram', 'Kochi',  15000);

-- SQLite only: with a single max(), the bare columns come from the row that has the maximum.
SELECT city, max(amount_paise) AS largest_paise, customer, id
FROM orders
GROUP BY city
ORDER BY city;

┌────────┬───────────────┬──────────┬────┐
│  city  │ largest_paise │ customer │ id │
├────────┼───────────────┼──────────┼────┤
│ Jaipur │ 8000          │ Meera    │ 4  │
│ Kochi  │ 15000         │ Vikram   │ 6  │
│ Pune   │ 99000         │ Tenzin   │ 5  │
└────────┴───────────────┴──────────┴────┘

For Pune the largest order is 99000 paise, and customer and id come from that row: Tenzin, order 5. This works only in SQLite, and if two rows tie for the maximum it picks one of them arbitrarily. In PostgreSQL and MySQL you get “the row with the largest value per group” with a window function or a join, which later modules cover.

Functional dependence and ANY_VALUE

Both PostgreSQL and MySQL accept an ungrouped column when it is functionally dependent on the grouped ones, which in practice means you grouped by a table’s primary key: one key value means one row, so every other column of that table has a single value per group. (PostgreSQL recognises only the primary key; MySQL also accepts a UNIQUE column declared NOT NULL.) This matters once you join tables: grouping by customers.id lets you select customers.name as well.

When you know a column has the same value in every row of each group but the engine cannot prove it, PostgreSQL (from version 16) and MySQL offer any_value(column): it returns a value from the group and tells the engine you accept any of them. SQLite 3.49 has no any_value(); its bare columns already behave that way.

A common mistake: grouping by too much

When an engine complains that customer is not grouped, adding it to GROUP BY makes the error go away. It also changes the question:

CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Asha',   'Pune',   45000),
  (2, 'Vikram', 'Kochi',  12000),
  (3, 'Asha',   'Pune',   30000),
  (4, 'Meera',  'Jaipur',  8000),
  (5, 'Tenzin', 'Pune',   99000),
  (6, 'Vikram', 'Kochi',  15000);

-- Meant: orders per city. Adding customer to GROUP BY "to make the error go away"
-- changes the question: now each group is one customer in one city.
SELECT city, customer, count(*) AS orders
FROM orders
GROUP BY city, customer
ORDER BY city, customer;

┌────────┬──────────┬────────┐
│  city  │ customer │ orders │
├────────┼──────────┼────────┤
│ Jaipur │ Meera    │ 1      │
│ Kochi  │ Vikram   │ 2      │
│ Pune   │ Asha     │ 2      │
│ Pune   │ Tenzin   │ 1      │
└────────┴──────────┴────────┘

Now each group is one customer in one city, so Pune is split into two rows and no row shows Pune’s total of three orders. Every column in GROUP BY makes the groups smaller. List exactly the columns that define “one row per …” in your question, and aggregate the rest.

How the engines differ

Key takeaways

• GROUP BY turns the rows that share the same grouped values into one output row, and the aggregates are computed per group.
• You can group by several columns and by expressions; each different combination of values is a group, and NULLs form one group.
• Every selected column must be grouped, aggregated or functionally dependent on a grouped primary key. PostgreSQL and MySQL enforce this; SQLite returns a value from an arbitrary row of the group instead.
• In SQLite only, a single min() or max() makes the bare columns come from the row that holds it.
• Sort grouped results with ORDER BY, and do not add columns to GROUP BY just to silence an error: it changes what each row means.