So far you have grouped by raw columns — department names, roles, statuses. Real reporting rarely stops there. A manager asks "how many people did we hire each year?" but the table stores full dates like '2019-03-11', not years. She asks "what is the average salary in each pay band?" but the table stores exact salaries, not bands. Grouping by expressions lets you bucket rows by computed values rather than by the raw data stored in a column.
An expression in GROUP BY can be a string function like SUBSTR, a date extraction, a mathematical formula, or a CASE that categorises rows into custom buckets. The database evaluates the expression for every row, groups rows that produce the same result, and then runs aggregates inside each bucket. The result is one row per computed category, exactly as if that category were a real column in the table.
flowchart TB A["2019-03-11<br/>2020-07-01<br/>2021-09-15"] --> S["SUBSTR(hired_on,1,4)"] S --> B1["2019"] S --> B2["2020"] S --> B3["2021"] B1 -->|COUNT| C1["1"] B2 -->|COUNT| C2["1"] B3 -->|COUNT| C3["1"] style S fill:#0e7490,color:#fff
Grouping by string and date expressions
SQLite does not have a dedicated YEAR() function, but SUBSTR(hired_on, 1, 4) extracts the first four characters of a date string, which is exactly the year. You can place that expression directly in the GROUP BY clause: GROUP BY SUBSTR(hired_on, 1, 4). Every row is evaluated, rows sharing the same year land in the same bucket, and aggregates like COUNT and AVG run inside each bucket.
The same idea works for month (SUBSTR(hired_on, 1, 7) gives "2021-09"), for prefixes (SUBSTR(role, 1, 3) gives "Eng" or "Sal"), or for any transformation that turns a detailed value into a category. The rule is simple: the expression in GROUP BY must match the expression in SELECT, character for character, so the database knows which computed column defines each group.
SELECT SUBSTR(hired_on, 1, 4) AS hire_year, COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees WHERE department_id IS NOT NULL GROUP BY SUBSTR(hired_on, 1, 4) ORDER BY hire_year;
Common expressions for grouping
Besides SUBSTR, you can group by mathematical expressions such as salary / 100000 to create rough hundred-thousand brackets, or by LENGTH(name) to see how name length correlates with pay. You can even group by boolean expressions: GROUP BY salary > 600000 produces two buckets — 0 for false and 1 for true — though a CASE expression is clearer for human readers.
The key is that any expression that turns a detailed value into a category is fair game. If you can write it in a SELECT list, you can usually place it in GROUP BY as well, as long as it does not reference an aggregate.
Grouping by CASE expressions
When the categories are not extractable from a string, you build them with CASE. A salary band is a classic example: no column stores "Low" or "High", so you manufacture the band inside the query itself. CASE WHEN salary < 500000 THEN 'Low' WHEN salary < 700000 THEN 'Medium' ELSE 'High' END evaluates every employee, sorts them into one of three buckets, and GROUP BY gathers the buckets.
CASE in GROUP BY is powerful because the categories are entirely up to you. You can group by decade, by project size, by employment status, or by any combination of columns. The only constraint is that the expression in SELECT must exactly match the expression in GROUP BY. A single typo — an extra space, a different alias — and the database will refuse the query.
flowchart LR A["All employees"] --> C["CASE WHEN salary < 500000<br/>THEN 'Low'<br/>WHEN salary < 700000<br/>THEN 'Medium'<br/>ELSE 'High' END"] C --> B1["Low"] C --> B2["Medium"] C --> B3["High"] B1 -->|COUNT| N1["3"] B2 -->|COUNT| N2["5"] B3 -->|COUNT| N3["6"] style C fill:#0e7490,color:#fff style B1 fill:#b45309,color:#fff style B2 fill:#0e7490,color:#fff style B3 fill:#0e7490,color:#fff
SELECT
CASE
WHEN salary < 500000 THEN 'Low'
WHEN salary < 700000 THEN 'Medium'
ELSE 'High'
END AS pay_band,
COUNT(*) AS headcount,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY CASE
WHEN salary < 500000 THEN 'Low'
WHEN salary < 700000 THEN 'Medium'
ELSE 'High'
END
ORDER BY min_salary;
Keeping expressions aligned
The most common mistake when grouping by expression is letting the SELECT version drift from the GROUP BY version. An extra space, a different constant, or a swapped THEN/ELSE clause splits the groups in ways you did not intend. The database groups by whatever is written in GROUP BY, then prints whatever is written in SELECT, and if the two differ you get mismatched labels and silently wrong counts.
Always copy the expression from SELECT into GROUP BY verbatim. If the expression is long, define it in both places exactly the same way. Do not try to optimise by shortening one side; the correctness of your report depends on perfect alignment.
Expression groups with multiple aggregates
Once you have built a computed group, you can stack as many aggregates inside it as you like. A query grouped by hire year can count arrivals, average their salaries, and find the highest pay in that cohort, all in one pass. The database only groups once, then computes every aggregate per bucket.
This is how you answer questions that no raw column can answer directly: "Which salary band has the widest gap between its highest and lowest earner?" or "Which hire year brought in the most expensive talent?" The expression creates the category, and the aggregates tell the story inside it.
SELECT
CASE
WHEN salary < 500000 THEN 'Low'
WHEN salary < 700000 THEN 'Medium'
ELSE 'High'
END AS pay_band,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary,
MAX(salary) - MIN(salary) AS pay_gap
FROM employees
GROUP BY CASE
WHEN salary < 500000 THEN 'Low'
WHEN salary < 700000 THEN 'Medium'
ELSE 'High'
END
ORDER BY avg_salary;
Sorting expression groups
Once you have computed groups, ORDER BY arranges them just as it arranges raw columns. You can sort by the expression itself, by any aggregate, or by the alias if your database supports it. Sorting by AVG(salary) DESC ranks your salary bands from richest to poorest; sorting by hire_year ASC shows chronological growth.
When grouping by CASE bands, the default order is arbitrary, so always add an explicit ORDER BY. Otherwise the bands may bounce around between runs, making a dashboard look unstable.
flowchart LR A["SELECT CASE WHEN salary < 500000<br/>THEN 'Low' ELSE 'High' END"] --> G["GROUP BY CASE WHEN salary < 500000<br/>THEN 'Low' ELSE 'High' END"] G --> R["One row per band"] style G fill:#0e7490,color:#fff style R fill:#0e7490,color:#fff
Group employees by the year they were hired and return hire_year and headcount. Order by year ascending.
SELECT -- extract year and count FROM employees -- group and order here ;
Use SUBSTR(hired_on, 1, 4) to extract the year.
Group by the same expression, then ORDER BY hire_year.
SELECT SUBSTR(hired_on, 1, 4) AS hire_year, COUNT(*) AS headcount FROM employees GROUP BY SUBSTR(hired_on, 1, 4) ORDER BY hire_year;
Group employees into salary bands (Low for under 500000, Medium for 500000 to 699999, High for 700000 and above). Return pay_band, headcount, and avg_salary. Order by avg_salary ascending.
SELECT -- CASE band here COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees -- group by the same CASE expression -- order by avg_salary ;
Use CASE WHEN salary < 500000 THEN 'Low' WHEN salary < 700000 THEN 'Medium' ELSE 'High' END.
Repeat the exact CASE expression in GROUP BY.
Order by AVG(salary) or the alias.
SELECT CASE WHEN salary < 500000 THEN 'Low' WHEN salary < 700000 THEN 'Medium' ELSE 'High' END AS pay_band, COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees GROUP BY CASE WHEN salary < 500000 THEN 'Low' WHEN salary < 700000 THEN 'Medium' ELSE 'High' END ORDER BY avg_salary;
This query tries to group employees into two pay bands, but the GROUP BY expression uses a different threshold than the SELECT expression. The result is silently wrong. Fix it by making both expressions identical.
SELECT CASE WHEN salary < 500000 THEN 'Low' ELSE 'High' END AS pay_band, COUNT(*) AS headcount FROM employees GROUP BY CASE WHEN salary < 600000 THEN 'Low' ELSE 'High' END;
The SELECT says < 500000, but the GROUP BY says < 600000.
Both expressions must match exactly. Change the GROUP BY threshold to 500000.
SELECT CASE WHEN salary < 500000 THEN 'Low' ELSE 'High' END AS pay_band, COUNT(*) AS headcount FROM employees GROUP BY CASE WHEN salary < 500000 THEN 'Low' ELSE 'High' END;
Group departments by whether they have a city or not. Return location_type ('Remote' if city is NULL, otherwise 'On-site') and dept_count. Order by dept_count descending.
SELECT -- CASE for location_type COUNT(*) AS dept_count FROM departments -- group and order here ;
CASE WHEN city IS NULL THEN 'Remote' ELSE 'On-site' END defines the band.
Repeat the exact CASE in GROUP BY.
ORDER BY dept_count DESC.
SELECT CASE WHEN city IS NULL THEN 'Remote' ELSE 'On-site' END AS location_type, COUNT(*) AS dept_count FROM departments GROUP BY CASE WHEN city IS NULL THEN 'Remote' ELSE 'On-site' END ORDER BY dept_count DESC;
For employees with a department, group by the decade they were hired (2010s for before 2020, 2020s for 2020 and later). Return decade, headcount, and avg_salary. Order by decade.
SELECT -- decade CASE here COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees -- filter, group, and order here ;
Use SUBSTR(hired_on, 1, 4) to extract the year, then compare to '2020'.
Filter with WHERE department_id IS NOT NULL.
Repeat the exact CASE expression in GROUP BY.
SELECT CASE WHEN SUBSTR(hired_on, 1, 4) < '2020' THEN '2010s' ELSE '2020s' END AS decade, COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees WHERE department_id IS NOT NULL GROUP BY CASE WHEN SUBSTR(hired_on, 1, 4) < '2020' THEN '2010s' ELSE '2020s' END ORDER BY decade;
Recap
GROUP BY expressionbuckets rows by computed values such as date parts, string prefixes, or CASE bands.- The expression in SELECT must exactly match the expression in GROUP BY to avoid silently wrong labels.
- CASE in GROUP BY creates custom categories that do not exist as raw columns.
- For portable SQL, repeat the full expression in GROUP BY rather than relying on the SELECT alias.
- Expression groups work with any aggregate: COUNT, AVG, MIN, MAX, and SUM.
- Always add ORDER BY when grouping by computed bands, because the default order is unpredictable.
Next you will combine everything you have learned — joins, aggregates, GROUP BY, and conditional logic — into a complete company report.