Skip to content
Back to DatabasesAll lessons

Databases

Aggregation (GROUP BY / COUNT / SUM)

O(n) time — the database scans every row once and adds it to a bucket. O(k) space for k distinct groups.

The idea, in plain English

GROUP BY is like sorting a pile of receipts into one envelope per customer, then writing a total on each envelope. Instead of one row per receipt, you get one row per customer, with a count and a sum.

How it works

  1. 1Decide what to group by, such as 'customer'. Every row with the same customer goes in the same bucket.
  2. 2For each bucket, run an aggregate function. COUNT(*) counts how many rows landed there. SUM(amount) adds up a column.
  3. 3Output one summary row per bucket, for example 'Ana: 2 orders, $70 total'.

When you'd use it

Use GROUP BY any time you want a total, average, or count per category. For example: total sales per customer, orders per day, or average score per student.

Common beginner mistakes

  • Do not select a plain column that is not in GROUP BY and not wrapped in an aggregate function. SQL rejects this. Some lenient databases instead pick an arbitrary value without warning you.
  • Do not assume groups come back in a predictable order. Sort them yourself, as we do below, if the order matters to you.

Try it — edit and run

Click the code to edit · press ⌘/Ctrl+↵ to run

Editable code. Tab and Shift+Tab indent. Press Escape, then Tab, to move focus out of the editor.

Expected output — hit Run to try it
SQL: SELECT customer, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY customer
Ana orders=2 total=70
Bilal orders=2 total=40
Cara orders=1 total=70

Not sure this is the right topic? See the learning paths → or where this leads →