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 webshop_order_line table:

1select
2    order_id
3from
4    {{raw.e_commerce_sample.webshop_order_line}}

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

1select
2    max(price)
3from
4    {{raw.e_commerce_sample.webshop_order_line}}

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

1select
2    order_id
3  , max(price)
4from
5    {{raw.e_commerce_sample.webshop_order_line}}

This query fails because it’s asking for different numbers of rows in the two columns, both in the same table.

The order_id column is trying to show you 999 rows, while the max (price) 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:

1select
2    order_id
3  , max(price)
4from
5    {{raw.e_commerce_sample.webshop_order_line}}
6group by
7    order_id

Which should return something like this: