Multi-Level Groups

We're still working at devils.fyi, a website similar to levels.fyi.

We know how to get aggregations per-company and per-department. However, our users are starting to ask for more detailed aggregations. They want to compare average salaries between the departments separately for each company. In other words, it's not enough to know the average salary at Poogle overall, or the average salary of anyone in marketing. People want to compare infrastructure at Poogle versus marketing at Poogle versus marketing at Wamazon, etc.

Luckily, GROUP BY can accept multiple columns.

SELECT
  company,
  department,
  avg(salary) AS avg_salary
FROM employees
GROUP BY company, department
ORDER BY company, department;

This means that the combination of the values in those 2 columns delimits the group, i.e. all rows where both of those columns' values are equal are grouped together.

Column Reference Shortcut

The GROUP BY and ORDER BY clauses have a convenient shortcut where you can replace column names with a number which is their index in the SELECT clause.

SELECT
  company,
  department,
  avg(salary) AS avg_salary
FROM employees
GROUP BY 1, 2
ORDER BY 1, 2;

GROUP BY 1, 2 is the same as GROUP BY company, department. The 1 means company because it is the first column to appear in the SELECT clause.