GROUP BY CASE with overlapping ranges

Question

Table:

CREATE TABLE companies (name VARCHAR(100) NOT NULL, number_of_employees INT NOT NULL, yearly_revenue DECIMAL(30, 2) NOT NULL);
INSERT INTO companies(name, number_of_employees, yearly_revenue) VALUES ('A', 5, 100), ('B', 5, 150), ('C', 15, 200), ('D', 25, 500), ('E', 25, 800);

If I want to count the companies, grouped by the number of employees, then I could write this query:

SELECT
    COUNT(*) AS count,
    CASE
        WHEN number_of_employees BETWEEN 0 AND 10 THEN '0-10'
        WHEN number_of_employees BETWEEN 10 AND 20 THEN '10-20'
        WHEN number_of_employees > 20 THEN '20-INF'
    END AS number_of_employees
FROM companies
GROUP BY 
    CASE
        WHEN number_of_employees BETWEEN 0 AND 10 THEN '0-10'
        WHEN number_of_employees BETWEEN 10 AND 20 THEN '10-20'
        WHEN number_of_employees > 20 THEN '20-INF'
    END

It would output:

count   number_of_employees
------------------------------
2       0-10
1       10-20
2       20-INF

Now my goal is to count the number of companies when grouping ranges overlap. Example query to illustrate my goal:

SELECT
    COUNT(*) AS count,
    CASE
        WHEN number_of_employees > 0 THEN '> 0'
        WHEN number_of_employees > 10 THEN '> 10'
        WHEN number_of_employees > 20 THEN '> 20'
    END AS number_of_employees
FROM companies
GROUP BY 
    CASE
        WHEN number_of_employees > 0 THEN '> 0'
        WHEN number_of_employees > 10 THEN '> 10'
        WHEN number_of_employees > 20 THEN '> 20'
    END

It would output:

count   number_of_employees
------------------------------
5       > 0

But my desired result looks like this:

count   number_of_employees
------------------------------
5       > 0
3       > 10
2       > 20

How should I achieve this?

Bonus question: apart from COUNT(*), I'd also like to know the answer for other aggregation functions, in particular AVERAGE.

user_1144035 · Answer

I think I would use a cumulative sum for this particular problem:

SELECT (CASE WHEN number_of_employees > 0 THEN '> 0'
             WHEN number_of_employees > 10 THEN '> 10'
             WHEN number_of_employees > 20 THEN '> 20'
        END) AS grouping
       SUM(COUNT(*)) OVER (ORDER BY MIN(number_of_employees)) AS count   
FROM companies
GROUP BY grouping
ORDER BY MIN(number_of_employees);

Dems_53341 · Answer

Join your data on to a mapping table, and group by the mapping table columns.

SELECT
  map.label,
  COUNT(*)
FROM
(
  SELECT  0 AS min_emp, '>  0' AS label
  UNION ALL
  SELECT 10 AS min_emp, '> 10' AS label
  UNION ALL
  SELECT 20 AS min_emp, '> 20' AS label
)
  AS map
LEFT JOIN
  companies
    ON companies.number_of_employees > map.min_emp
GROUP BY
  map.label

In this way, a company with 21 employees is joined on to all three categories, but a company with just 1 employee is only joined on the the first category.

You can then even have arbitrary ranges. (Note, I use >= AND < rather than BETWEEN because it works much more flexibly with non-integer values, such as dates or decimals.)

SELECT
  map.label,
  COUNT(*)
FROM
(
  SELECT  0 AS min_emp, 15 AS max_emp, '>=  0 AND < 15' AS label
  UNION ALL
  SELECT 10 AS min_emp, 25 AS max_emp, '>= 10 AND < 25' AS label
  UNION ALL
  SELECT 20 AS min_emp, 99 AS max_emp, '>= 20 AND < 99' AS label
)
  AS map
LEFT JOIN
  companies
    ON  companies.number_of_employees >= map.min_emp
    AND companies.number_of_employees <  map.max_emp
GROUP BY
  map.label

GROUP BY CASE with overlapping ranges

2 Answers