lesson 11 Calculations & data quality · intermediate

Group into summaries

Summarize rows that share the same group.

the idea

GROUP BY forms one group for each key. Aggregates then summarize each group. Select grouping columns and aggregates to avoid ambiguous output.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

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

Watch for: Selecting an ungrouped name in SQLite can return an arbitrary member of the group.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Read product rows. Two products belong to each category. The input grain is one product.

   step-1.sql Run

   sql›

   SELECT id,name,category
   FROM products;

2. 2

   Count within groups. GROUP BY changes the grain to one category.

   step-2.sql Run

   sql›

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