$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Grouping rows with GROUP BY #

The aggregate functions we've seen so far (COUNT, SUM, AVG, MIN, MAX) collapse an entire table into a single row. But often you want a separate summary for each distinct value in a column, for example, "how many products are in each category?" That's what GROUP BY is for.

SELECT column_name, AGGREGATE_FUNCTION(other_column)
FROM table_name
GROUP BY column_name;

GROUP BY column_name splits the rows into buckets, one per distinct value of column_name, and the aggregate function is then computed separately within each bucket instead of across the whole table.

Example #

SELECT
    gender,
    COUNT(*) AS total
FROM
    customers
GROUP BY
    gender;

Result:

+--------+-------+
| gender | total |
+--------+-------+
|   F    |   4   |
|   M    |   6   |
+--------+-------+

Instead of one count for the whole table, we get one count per distinct gender value.

Exercise #

Write a command that, for each category in the products table, counts how many products belong to it.

  • The result has a category column and a total column.
  • Each category shows the correct count of products in that category.

Note: in this particular dataset every category happens to have exactly 2 products, so the counts will look uniform here. In a real dataset, different categories would usually have different counts.

Solution 
SELECT category, COUNT(*) AS total
FROM products
GROUP BY category;
Output will be displayed here