SQL Academy · Lesson

CASE in ORDER BY and Aggregates

Conditional sorting and counting.

CASE in ORDER BY and Aggregates is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Sorting with Custom Priority

SQL's ORDER BY clause normally sorts rows by a column's natural value. But you can embed a CASE expression inside ORDER BY to create a completely custom sort order — one that no single column could produce on its own.

This technique is powerful when business rules determine priority rather than raw data values.

SELECT product_name, status
FROM products
ORDER BY
  CASE status
    WHEN 'urgent'   THEN 1
    WHEN 'active'   THEN 2
    WHEN 'pending'  THEN 3
    ELSE                 4
  END;

How CASE Inside ORDER BY Works

When the database evaluates ORDER BY CASE ... END, it computes an integer (or any comparable value) for every row. Rows are then sorted by that computed value instead of — or in addition to — a raw column.

The CASE expression is not stored; it exists only for the duration of the query.

SELECT order_id, priority
FROM orders
ORDER BY
  CASE priority
    WHEN 'high'   THEN 1
    WHEN 'medium' THEN 2
    WHEN 'low'    THEN 3
    ELSE               9
  END,
  order_id;

Sorting NULLs Explicitly

By default, different databases place NULLs at the beginning or end of a sorted result in different ways. A CASE in ORDER BY lets you decide exactly where NULLs land — regardless of the database engine.

SELECT employee_name, manager_id
FROM employees
ORDER BY
  CASE WHEN manager_id IS NULL THEN 0 ELSE 1 END,
  manager_id;

Conditional Ascending and Descending

A CASE in ORDER BY can also mimic conditional direction. By mapping category values to negative numbers you can effectively reverse sort order for specific groups while keeping normal order for others.

This is useful when different row types need different sort logic in a single result set.

SELECT task_name, due_date, is_overdue
FROM tasks
ORDER BY
  CASE WHEN is_overdue = 1 THEN 0 ELSE 1 END,
  due_date;

CASE Inside COUNT

You can embed a CASE expression inside an aggregate function like COUNT. The trick is to return a non-NULL value for rows you want to count, and NULL for rows you want to skip — because COUNT ignores NULLs.

SELECT
  COUNT(CASE WHEN status = 'active'   THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
FROM users;

CASE Inside SUM — Conditional Totals

Placing CASE inside SUM lets you add up values only when a condition is true, and treat non-matching rows as zero. This is a very common pattern for pivot-style reports that break a single column into multiple metric columns.

SELECT
  SUM(CASE WHEN region = 'North' THEN sales_amount ELSE 0 END) AS north_total,
  SUM(CASE WHEN region = 'South' THEN sales_amount ELSE 0 END) AS south_total,
  SUM(CASE WHEN region = 'East'  THEN sales_amount ELSE 0 END) AS east_total
FROM sales;

Conditional AVG with CASE

The same pattern works with AVG. Because AVG ignores NULLs, returning NULL for rows you want to exclude produces the average of only the matching rows — no subquery needed.

SELECT
  AVG(CASE WHEN department = 'Engineering' THEN salary END) AS avg_eng_salary,
  AVG(CASE WHEN department = 'Marketing'   THEN salary END) AS avg_mkt_salary
FROM employees;

Grouping with CASE — Bucketing Rows

You can use CASE in the GROUP BY or SELECT list to bucket continuous values into categories, then aggregate each bucket. This turns a numeric column into labelled groups without altering the underlying data.

SELECT
  CASE
    WHEN age < 18             THEN 'Under 18'
    WHEN age BETWEEN 18 AND 35 THEN '18-35'
    WHEN age BETWEEN 36 AND 55 THEN '36-55'
    ELSE                            '56+'
  END AS age_group,
  COUNT(*) AS user_count
FROM users
GROUP BY
  CASE
    WHEN age < 18             THEN 'Under 18'
    WHEN age BETWEEN 18 AND 35 THEN '18-35'
    WHEN age BETWEEN 36 AND 55 THEN '36-55'
    ELSE                            '56+'
  END;

Using an Alias vs Repeating CASE

Repeating a long CASE expression in both SELECT and GROUP BY is verbose. Some databases (MySQL, PostgreSQL with workarounds) let you reference the alias in ORDER BY, but not in GROUP BY. The safest portable approach is to wrap the query in a subquery or CTE and GROUP BY the alias from there.

WITH scored AS (
  SELECT
    customer_id,
    CASE
      WHEN total_spent >= 1000 THEN 'Gold'
      WHEN total_spent >= 500  THEN 'Silver'
      ELSE                          'Bronze'
    END AS tier
  FROM customers
)
SELECT tier, COUNT(*) AS customer_count
FROM scored
GROUP BY tier
ORDER BY
  CASE tier
    WHEN 'Gold'   THEN 1
    WHEN 'Silver' THEN 2
    ELSE               3
  END;

Combining ORDER BY CASE with ASC / DESC

After the CASE expression you can still append ASC or DESC, and add more sort columns separated by commas. The CASE score is just the first sort key; subsequent columns break ties in the normal way.

SELECT
  ticket_id,
  category,
  created_at
FROM support_tickets
ORDER BY
  CASE category
    WHEN 'billing'  THEN 1
    WHEN 'outage'   THEN 2
    WHEN 'feature'  THEN 3
    ELSE                 4
  END ASC,
  created_at ASC;

Real-World Example — Sales Dashboard

Here is a realistic query that combines a CASE-based aggregate with a CASE-based sort. It produces a per-region sales summary ordered so the highest-revenue region always appears first, with 'Other' pushed to the bottom.

SELECT
  CASE
    WHEN region IN ('North', 'South', 'East', 'West') THEN region
    ELSE 'Other'
  END AS region_label,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_sales,
  COUNT(CASE WHEN status = 'refunded' THEN 1 END)           AS refund_count
FROM orders
GROUP BY
  CASE
    WHEN region IN ('North', 'South', 'East', 'West') THEN region
    ELSE 'Other'
  END
ORDER BY completed_sales DESC;

Knowledge Check

Test your understanding of using CASE inside ORDER BY and aggregate functions.

Lesson Recap

In this lesson you learned how to combine CASE with ORDER BY and aggregate functions:

CASE in ORDER BY — assign custom sort scores to rows, control where NULLs appear, and mix multiple sort rules in one query.
CASE in COUNT — return a non-NULL value for rows to include, NULL (implicitly or explicitly) for rows to skip.
CASE in SUM / AVG — use 0 or NULL as the ELSE value to build conditional totals and averages in a single pass.
CASE in GROUP BY — bucket continuous or categorical values into labelled groups for aggregation.

These patterns eliminate many subqueries and make pivot-style reports straightforward to write in plain SQL.

Frequently asked questions

Is the “CASE in ORDER BY and Aggregates” lesson free?

Yes — the full text of “CASE in ORDER BY and Aggregates” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “CASE in ORDER BY and Aggregates”?

Conditional sorting and counting. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “CASE in ORDER BY and Aggregates” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy