Antipattern: Reference Non-grouped Columns
Explore the common SQL antipattern of referencing non-grouped columns in GROUP BY queries. Understand the Single-Value Rule and why selecting columns outside aggregation or grouping causes errors. This lesson clarifies how to write unambiguous queries and avoid misconceptions with aggregate functions such as MAX().
We'll cover the following...
The root cause of this antipattern is simple, and it reveals a common misconception that many programmers have about how grouping queries work in SQL.
The single-value rule
The rows in each group consist only of items that have the same value in the column or columns we name after GROUP BY. For example, in the following query, there is a one-row group for each distinct value in product_id.
Every column in the select list of a query must have a single-value row per row group. This is called the Single-Value Rule. Columns named in the GROUP BY clause are guaranteed to ...