Grouping by Dates

"How many people did we hire each year?" To answer it, group by a piece of the date.

strftime('%Y', hire_date) pulls out the year, so everyone hired in the same year lands in the same group:

SELECT strftime('%Y', hire_date) AS year,
       COUNT(*) AS hires
FROM employees
GROUP BY year
ORDER BY year;

Use '%Y-%m' for monthly groups, like '2020-03':

SELECT strftime('%Y-%m', hire_date) AS month,
       COUNT(*) AS hires
FROM employees
GROUP BY month
ORDER BY month;

You can group by more than one column. Each unique combination becomes its own group:

SELECT department_id,
       strftime('%Y', hire_date) AS year,
       COUNT(*) AS hires
FROM employees
GROUP BY department_id, year
ORDER BY department_id, year;

Other databases use different date functions (EXTRACT, DATE_TRUNC or YEAR), but the idea is the same.