GROUP BY one and many columns

Part of the free LogicWiz SQL & Databases course, Module 4: Aggregates and GROUP BY. The queries and practice run in the app on real Postgres.

Aarav knows there are 24 orders. Now the kitchen asks: how many per restaurant? Running one COUNT with a different WHERE for each of 8 restaurants would work, until there are 800.

You'll learn to

• Split rows into groups with GROUP BY and summarise each group
• Group by two columns, and by an expression like the date of a timestamp
• Follow the rule: every plain column in SELECT must be in GROUP BY

Learn

Buckets, then a summary of each bucket

GROUP BY status sorts the rows into buckets, one per different value of status. The aggregate then runs inside each bucket, and you get one result row per bucket.

Think of a kirana shop owner sorting the day's bills into piles by payment type (cash, UPI, card) and then adding up each pile. The pile is the group; the total is the aggregate.

Orders per status

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

Three different statuses, so three rows. status can be shown because it is the same for every row in its bucket.

What GROUP BY does with the rows

Each row lands in the bucket for its status. COUNT(*) then counts each bucket: 20, 2 and 2.

Grouping by two columns

SELECT city, is_premium, COUNT(*) AS customers
FROM customers
GROUP BY city, is_premium
ORDER BY city, is_premium;

With two columns, each different pair is a bucket: (Bengaluru, true), (Bengaluru, false), and so on. Chennai has only non-premium customers, so it gets one row, not two.

Grouping by an expression

SELECT ordered_at::date AS order_date,
  COUNT(*) AS orders,
  SUM(delivery_fee) AS fees
FROM orders
GROUP BY ordered_at::date
ORDER BY order_date;

Grouping by ordered_at itself would give one bucket per minute. Grouping by ordered_at::date puts the whole day in one bucket. DATE_TRUNC('month', ordered_at) would do the same per month.

Every column in SELECT that is not inside an aggregate must be in GROUP BY. SELECT city, is_premium, COUNT(*) … GROUP BY city fails: a city bucket has both premium and regular customers, so which is_premium would Postgres show? Add the column to GROUP BY, or wrap it in an aggregate.

NULL gets its own group

Three orders have no delivery partner. GROUP BY partner_id puts all three NULLs together in one group, even though NULL is never equal to NULL in a WHERE.

So that group shows up as a row with a blank partner_id. If you do not want it, filter it out with WHERE partner_id IS NOT NULL before grouping.

Older MySQL setups (with ONLY_FULL_GROUP_BY turned off) let you select a column that is not grouped and quietly pick any value from the group. Postgres refuses. Interviewers like asking why the Postgres way is safer: the MySQL answer can change from run to run.

In one line

GROUP BY makes one row per bucket; every plain column in SELECT must be in GROUP BY; NULLs form one group.