Module 13
Group by and aggregation
Summaries with sums, counts, averages and all-rows tables.
You'll be able to
- Group with several aggregations
- Count distinct values
- Sort grouped output
Lessons
- 1.Group by with several aggregationsBuild summary tables with the right aggregation for each question.
- 2.All Rows grouping and per-group calculationsRun a calculation inside each group and bring the detail rows back.
- 3.Group by several keys and sort the outputChoose the grain of a summary table and present it in a deliberate order.
- 4.Group consecutive rows with GroupKind.LocalSummarise runs of consecutive values instead of all occurrences of a value.
Take the group by and aggregation quiz (8 questions)
Exercises
- intermediate~8 minPro
Summarise orders by region (kite shop)
Group by Region with order count, total quantity and average unit price, sorted by region.
- intermediate~8 minPro
Summarise orders by region (bakery)
Group by Region with order count, total quantity and average unit price, sorted by region.
- intermediate~8 minPro
Summarise orders by region (bike rental)
Group by Region with order count, total quantity and average unit price, sorted by region.
- intermediate~8 minPro
Count distinct customers per product (kite shop)
For each product, count how many different customers ordered it.
- intermediate~8 minPro
Count distinct customers per product (bakery)
For each product, count how many different customers ordered it.
- intermediate~8 minPro
Count distinct customers per product (bike rental)
For each product, count how many different customers ordered it.
- advanced~12 minPro
Rank each employee's claims by amount (consultancy)
Within each employee, number claims from largest to smallest Amount.
- advanced~12 minPro
Rank each employee's claims by amount (school)
Within each employee, number claims from largest to smallest Amount.
- intermediate~8 minPro
Find each region's first and last order date (kite shop)
Group by Region with the earliest and latest OrderDate as real dates.
- intermediate~8 minPro
Find each region's first and last order date (bakery)
Group by Region with the earliest and latest OrderDate as real dates.
- intermediate~8 minPro
Find each region's first and last order date (bike rental)
Group by Region with the earliest and latest OrderDate as real dates.
- beginner~6 minPro
Summarise orders by region and product (kite shop)
Group by both Region and Product with the number of orders and the total quantity.
- beginner~6 minPro
Summarise orders by region and product (bakery)
Group by both Region and Product with the number of orders and the total quantity.
- beginner~6 minPro
Summarise orders by region and product (bike rental)
Group by both Region and Product with the number of orders and the total quantity.
- advanced~12 minPro
Find each region's best-selling product by revenue (kite shop)
Total revenue per region and product, then keep the top product of each region.
- advanced~12 minPro
Find each region's best-selling product by revenue (bakery)
Total revenue per region and product, then keep the top product of each region.
- advanced~12 minPro
Find each region's best-selling product by revenue (bike rental)
Total revenue per region and product, then keep the top product of each region.
- intermediate~8 minPro
Average quantity per region when some quantities are missing (kite shop)
Some orders have no quantity yet. Report orders, how many have a quantity, and the average of the known quantities.
- intermediate~8 minPro
Average quantity per region when some quantities are missing (bakery)
Some orders have no quantity yet. Report orders, how many have a quantity, and the average of the known quantities.
- intermediate~8 minPro
Average quantity per region when some quantities are missing (bike rental)
Some orders have no quantity yet. Report orders, how many have a quantity, and the average of the known quantities.
- intermediate~8 minPro
List each customer's products in one cell (kite shop)
One row per customer with their distinct products, sorted alphabetically and separated by commas.
- intermediate~8 minPro
List each customer's products in one cell (bakery)
One row per customer with their distinct products, sorted alphabetically and separated by commas.
- intermediate~8 minPro
List each customer's products in one cell (bike rental)
One row per customer with their distinct products, sorted alphabetically and separated by commas.
- beginner~6 minPro
Keep only customers with three or more orders (kite shop)
Count orders per customer and keep the customers who ordered at least three times, with their total quantity.
- beginner~6 minPro
Keep only customers with three or more orders (bakery)
Count orders per customer and keep the customers who ordered at least three times, with their total quantity.
- beginner~6 minPro
Keep only customers with three or more orders (bike rental)
Count orders per customer and keep the customers who ordered at least three times, with their total quantity.