← All Kits · SQL Kit

GROUP BY with CASE: Grouping by a Rule Instead of a Column

Michael Nocito · Updated August 2026 · Every query on this page was run before it was published

The table has an amount. The report wants small, medium and large. Nobody is going to add a band column to the source table, and they should not: the boundaries change more often than the data does.

What you do: write the rule as a CASE in the SELECT, and repeat it in the GROUP BY. The band exists for the length of the query and nowhere else.

The short version. Group by the expression, not by the alias, and your query runs on every database.

The table

CREATE TABLE orders(order_id INT, customer_id INT, amount INT, status TEXT);
INSERT INTO orders VALUES
(101,1,120,'shipped'),(102,1,80,'shipped'),(103,2,200,'cancelled'),
(104,2,50,'shipped'),(105,3,300,'shipped'),(106,4,90,'pending'),
(107,4,60,'shipped'),(108,5,150,'cancelled');

The query

SELECT CASE WHEN amount >= 200 THEN 'large'
            WHEN amount >= 100 THEN 'medium'
            ELSE 'small' END AS band,
       COUNT(*)   AS orders,
       SUM(amount) AS total
FROM orders
GROUP BY CASE WHEN amount >= 200 THEN 'large'
              WHEN amount >= 100 THEN 'medium'
              ELSE 'small' END
ORDER BY total DESC;

Two orders of 200 and 300 make large. Two of 120 and 150 make medium. The remaining four, 80, 50, 90 and 60, make small. Eight rows in, three rows out, and 500 + 280 + 270 = 1,050, which is the full table total. That addition is the check worth doing every time.

Where the CASE can go, by database

The CTE version is the one to prefer as soon as the rule appears more than once:

WITH banded AS (
  SELECT order_id, amount,
         CASE WHEN amount >= 200 THEN 'large'
              WHEN amount >= 100 THEN 'medium'
              ELSE 'small' END AS band
  FROM orders
)
SELECT band, COUNT(*) AS orders, SUM(amount) AS total
FROM banded
GROUP BY band
ORDER BY total DESC;

The rule is written once, so it cannot drift between the SELECT and the GROUP BY. Two copies of a rule that must agree is exactly the kind of thing that gets half-edited six months later.

The band that disappears

Add a fourth band for anything at or above 500 and the query returns three rows, not four. GROUP BY reports the groups that exist in the data; a band nothing landed in produces no row rather than a zero.

For a report that must always show every band, supply the list and join to it:

WITH bands(band, sort_order) AS (
  VALUES ('small',1), ('medium',2), ('large',3), ('huge',4)
),
banded AS (
  SELECT CASE WHEN amount >= 500 THEN 'huge'
              WHEN amount >= 200 THEN 'large'
              WHEN amount >= 100 THEN 'medium'
              ELSE 'small' END AS band,
         amount
  FROM orders
)
SELECT b.band,
       COUNT(x.amount)              AS orders,
       COALESCE(SUM(x.amount), 0)   AS total
FROM bands b
LEFT JOIN banded x ON x.band = b.band
GROUP BY b.band, b.sort_order
ORDER BY b.sort_order;

Now huge appears with 0 and 0. Note COUNT(x.amount) rather than COUNT(*): after a LEFT JOIN the unmatched band still produces one row, and COUNT(*) would report 1 for a band containing nothing.

Sorting bands in a sensible order

Alphabetical order puts large before medium before small, which is meaningless. Two ways to fix it:

-- sort by a number, hide the number
ORDER BY CASE WHEN amount >= 200 THEN 3
              WHEN amount >= 100 THEN 2
              ELSE 1 END DESC

-- or carry a sort_order column alongside the band, as above

The second is better in anything that gets reused, because the ordering rule lives next to the band definition rather than in a third copy of the same conditions.

Grouping by a rule and a column together

SELECT status,
       CASE WHEN amount >= 200 THEN 'large'
            WHEN amount >= 100 THEN 'medium'
            ELSE 'small' END AS band,
       COUNT(*) AS orders
FROM orders
GROUP BY status,
         CASE WHEN amount >= 200 THEN 'large'
              WHEN amount >= 100 THEN 'medium'
              ELSE 'small' END
ORDER BY status, band;

One row per combination that occurs. With three statuses and three bands there are nine possible rows and considerably fewer real ones, which is normal and worth stating on the report so nobody reads a missing combination as a zero.

How to apply this to your own work

1. Check that your band totals sum to the ungrouped total. It is one extra query and it catches a missing ELSE.
2. Move any rule used more than once into a CTE, so there is a single definition.
3. Write the boundary policy in a comment: does a value equal to the boundary go up or down?
4. Decide whether empty bands must appear, and if so build the band list explicitly.
5. Give bands a deliberate sort order rather than accepting alphabetical.

The one habit to keep

Ask where the rule lives. A band written inside a query is invisible to everyone who does not read the query, so if the same rule appears in a spreadsheet and a dashboard as well, it will eventually disagree with itself. One definition, in one place, referenced everywhere.

How many places in your reporting define what counts as a large order?