>_ IzzyLab

Hands-on task

This task is step 9 of the course “SQL with SQLite”

SQLite: filtering groups with HAVING

medium~7 min

The database /root/shop.db has a table sales(id, category, amount). Find the categories whose total revenue is greater than 300, and write their names (one per line) to /root/out.txt.

What you'll practise:
  • sqlite3
  • having
  • group by
  • group filtering
  • aggregates

Topic breakdown

It is important to distinguish two filtering stages in SQL: WHERE filters out individual rows before grouping and does not see aggregates, while HAVING is applied after GROUP BY and filters the finished groups by an aggregate value. So a condition like "a group's total revenue is above a threshold" can only be expressed with HAVING. The clause order is fixed: grouping first, then the condition on the groups.

What's checked

  1. the file lists exactly the categories with revenue over 300

What it looks like