SQL tutorial •
Lesson
16
,
Aggregating Data

Avoid failing queries with aggregate functions and GROUP BY

When writing your queries, you have to be careful to make sure they don’t fail. One of the things that makes a query break is if you don’t ask for the same number of rows for different columns.

For example, this query will return all 999 rows of order IDs from the <span class="code">order_line</span> table:

And this query will return only the single highest price in the table:

But if you try to combine them, the query will fail:

This query fails because it’s asking for different numbers of rows in the two columns, both in the same table. The <span class="code">order_id</span> column is trying to show you 999 rows, while the <span class="code">max(price)</span> column is trying to show only 1 row. This won’t work.

When you’re working with aggregate functions, all columns in the query need to be either aggregated or grouped. This could look like this in a table, where you’d be shown the highest price for each individual order:

Next up:
Introduction to sub-queries and CTE