Weld logo
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 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

Next up

Introduction to sub-queries and CTE

Go to lesson
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.