CASE WHEN to SQL: create business categories

CASE WHEN translates a rule into a value

A report must often group raw values into understandable categories: small, medium or large order; active or inactive customer; objective achieved or not.

The CASE expression evaluates the conditions in order and returns the value associated with the first true condition. It creates a result column without modifying the source table.

The full syntax of CASE WHEN

Each branch begins with WHEN, describes a condition, and then reports the result after THEN. ELSE covers all remaining cases and END closes the expression.

An alias placed after END gives a usable name to the new column.

• CASE begins the expression.
• WHEN defines a condition.
• THEN provides the corresponding result.
• ELSE processes the other lines.
• END closes the expression.

SELECT
  id_commande,
  montant,
  CASE
    WHEN montant >= 150 THEN 'High'
    WHEN montant >= 50 THEN 'Average'
    ELSE 'Low'
  END AS tranche_montant
FROM commandes
ORDER BY id_commande ASC;

The order of the conditions determines the result

CASE stops at the first true condition. The thresholds must therefore be written from the most restrictive to the broadest in this example.

If montant >= 50 appeared before montant >= 150, an order of 200 would immediately satisfy the first branch and be classified as "Medium".

CASE
  WHEN montant >= 150 THEN 'High'
  WHEN montant >= 50 THEN 'Average'
  ELSE 'Low'
END

What happens without ELSE?

When neither condition is true and no ELSE is present, CASE returns NULL. This behavior may be voluntary, but it must be anticipated in subsequent filters and aggregations.

Adding ELSE often makes the rule easier to check. A label like “Unclassified” also reveals values ​​that do not fall into any expected category.

SELECT
  id_commande,
  CASE
    WHEN statut = 'Paid' THEN 'Completed'
    WHEN statut = 'Pending' THEN 'To process'
    ELSE 'To check'
  END AS suivi
FROM commandes;

Building a KPI with conditional aggregation

CASE can return 1 when the condition is true and 0 otherwise. SUM then adds these flags to count the rows that meet the rule.

This technique makes it possible to calculate several KPIs on a single line and a single scope, without multiplying queries.

• COUNT(*) gives the total volume.
• Each CASE produces a 0 or 1 indicator.
• SUM transforms these indicators into counters.

SELECT
  COUNT(*) AS nombre_commandes,
  SUM(CASE WHEN statut = 'Paid' THEN 1 ELSE 0 END) AS commandes_payees,
  SUM(CASE WHEN statut = 'Pending' THEN 1 ELSE 0 END) AS commandes_en_attente
FROM commandes;

Group results by calculated segment

A CASE expression can serve as a dimension in a report. By grouping it, you obtain one line per slice accompanied by a volume, a total or an average.

Depending on the SQL engine, the alias is not always accepted in GROUP BY. Repeating the expression remains an explicit and portable solution.

SELECT
  CASE
    WHEN montant >= 150 THEN 'High'
    WHEN montant >= 50 THEN 'Average'
    ELSE 'Low'
  END AS tranche_montant,
  COUNT(*) AS nombre_commandes,
  SUM(montant) AS chiffre_affaires
FROM commandes
GROUP BY
  CASE
    WHEN montant >= 150 THEN 'High'
    WHEN montant >= 50 THEN 'Average'
    ELSE 'Low'
  END
ORDER BY chiffre_affaires DESC;

CASE does not always replace WHERE

Use WHERE when the need is to simply remove rows. CASE is more suitable when it is necessary to produce a different value depending on the line or calculate several conditional indicators.

An unnecessarily nested CASE expression makes the rule difficult to reread. Prefer several simple branches and unambiguous category names.

• WHERE selects a perimeter.
• CASE classifies or transforms a value.
• CASE in SUM or COUNT constructs a conditional flag.
• A reference table becomes preferable when the rules are numerous and change often.

Check a CASE rule before publishing it

List the expected categories, test each exact threshold, and check the NULL values. Then compare the total segments to the number of records returned from the scope.

The most common errors are conditions in the wrong order, a missing ELSE, results of incompatible types, or thresholds that leave an interval uncovered.