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;The first WHEN condition that is true returns the corresponding THEN value. If no condition is true, the ELSE value is returns. If there is no ELSE, NULL is returned.
Works at least with: MariaDB, MySQL, PostgreSQL, Firebird, SQLite, Oracle, SQL Server, Informix, RedShift, Snowflake.
Indexes
To speed up this query, you need an index whose first column is salary.
Informix
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 NULL:
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;Reference
Informix documentation: