GROUP BY allows us to group identical values to aggregate data. What is not obvious is that it can be easily used to group values that belong to the same range, as well.
We can use the
CASE construct to The syntax is the same for all major DBMSs, with small exceptions.
Example: Count employees with different salary ranges (30 of them earn less than 40,000, etc).
SELECT CASE WHEN salary < 40000 THEN 1 WHEN salary < 60000 THEN 2 WHEN salary < 80000 THEN 3 ELSE 4 END AS salary_range, COUNT(*) FROM employees GROUP BY AS salary range;
WHEN condition that is true returns the corresponding
THEN value. If no condition is true, the
ELSE value is returns. If there is no
NULL is returned.
Works at least with: MariaDB, MySQL, PostgreSQL, Firebird, SQLite, Oracle, SQL Server, Informix, RedShift, Snowflake.
To speed up this query, you need an index whose first column is
Informix does not really support the
ELSE clause. You can only specify
ELSE NULL, which is the same as omitting it.
If necessary, you can use
nvl() to obtain a value other than
SELECT nvl(CASE WHEN salary < 40000 THEN 1 WHEN salary < 60000 THEN 2 WHEN salary < 80000 THEN 3 END, 4) AS salary_range, COUNT(*) FROM employees GROUP BY AS salary range;