Built-In Aggregate Part 2
Part of the Fundamentals section of Coddy's SQL journey — lesson 35 of 72.
Sometimes we need to use aggregate functions in more complex ways, such as comparing each row with an aggregate value. This is where nested queries come in handy.
When you want to perform calculations that combine individual rows with aggregate values, you need to use a nested query. Here's why:
This won't work:
SELECT value + MIN(value)
FROM table1The reason is that aggregate functions work on the entire column, while the 'value' reference is trying to work row by row. To solve this, we use a nested query:
SELECT value + (SELECT MIN(value) FROM table1)
FROM table1This works because the nested query (SELECT MIN(value) FROM table1) is executed first and returns a single value, which can then be used in the main query for each row.
Common use cases for nested aggregates:
1. Comparing each value to the average
2. Calculating percentage of total
3. Finding differences from maximum or minimum values
Challenge
EasyAvailable tables and columns:
<strong>items</strong>:<strong>id</strong>,<strong>price</strong>
Calculate how much each item's price exceeds the average price of all items. Display the item ID, its price, and the difference from the average. Name the difference column as diff_from_avg.
Order the results by the difference in descending order.
Cheat sheet
When combining individual rows with aggregate values, use nested queries because aggregate functions work on entire columns while regular columns work row by row.
This won't work:
SELECT value + MIN(value)
FROM table1Use a nested query instead:
SELECT value + (SELECT MIN(value) FROM table1)
FROM table1The nested query executes first and returns a single value that can be used in the main query for each row.
Common use cases:
- Comparing each value to the average
- Calculating percentage of total
- Finding differences from maximum or minimum values
Try it yourself
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Fundamentals
4More Keywords
The IN keywordThe BETWEEN keywordThe LIKE keywordThe AS keywordRecap - Cellphone Models
2Conditions
Conditions BasicsThe AND keywordThe OR keywordThe NOT keywordMultiple Conditions CombinedParenthesisBooleans5Arithmetic Operations
Mathematical OperatorsMathematical ColumnsThe Modulo OperationThe ROUND() Function3Specific Return Format
Null valuesSort Results Part 1Sort Results Part 2Recap - Cyber Security FirmLimit number of recordsRecap - Vehicle Factory6Intro Challenges
Recap - Parliamentary ElectionRecap - Police Criminal ArrestRecap - Bar Beverage ContainerRecap - Engineer new columns