Code / SQL with Data / Unit 4: Summaries: aggregates and GROUP BY

GROUP BY: a summary per group GROUP BY: हर समूह का सारांश

Lesson 9 of 13 · about 14 minutes · Count and add up per category, per product.

⚡ 0 XP

Step 1 of 2

One row per group

GROUP BY splits the rows into piles, then runs the aggregate on each pile:

SELECT category, COUNT(*) AS items FROM products GROUP BY category;

Imagine sorting the shop’s products into baskets by category, then counting each basket. You get one row per category.

Rule: every column in SELECT is either in GROUP BY or inside an aggregate like COUNT/SUM.

Example

SELECT category,
  MAX(price) AS top_price
FROM products
GROUP BY category;

🎯 Challenge: Items per category +15 XP

Show each category with the number of products in it, named items. Columns: category, items.

Try twice to unlock the solution (0/2)

query.sqlCtrl ⏎

Tables (practice database)

Results

Press Run to see your query’s rows here.

Checklist

Press Run to check your code.

Your rows match the expected answer
Uses GROUP BY

Lesson notes (text version)

1. One row per group

GROUP BY splits the rows into piles, then runs the aggregate on each pile:

SELECT category, COUNT(*) AS items FROM products GROUP BY category;

Imagine sorting the shop’s products into baskets by category, then counting each basket. You get one row per category.

Rule: every column in SELECT is either in GROUP BY or inside an aggregate like COUNT/SUM.

                SELECT category,
  MAX(price) AS top_price
FROM products
GROUP BY category;

Challenge: Items per category

Show each category with the number of products in it, named items. Columns: category, items.

2. Best-sellers

GROUP BY works with SUM too. Group sales by product_id and add up qty, and you know how many units of each product were sold.

You can sort by the alias of an aggregate: ORDER BY units DESC. When two products sold the same number, add a second sort column so the order is always the same: ORDER BY units DESC, product_id.

                SELECT day, SUM(qty) AS units
FROM sales
GROUP BY day;

Challenge: Units sold per product

From sales, show product_id and SUM(qty) named units, one row per product. Sort by units highest first, and for equal units by product_id smallest first.