Conditional Aggregation with Case/When
Branch logic inside a single query.
Conditional Aggregation with Case/When is a free Django 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 Django Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Branching Inside a Query
Sometimes you need different results for different rows. Case lets the database choose a value per row, like an if statement in SQL.
The Case and When Pair
A Case holds one or more When branches; each When pairs a condition with the value to use when that condition is true.
from django.db.models import Case, When, ValueA Simple Label
Annotate a computed label by giving each When a condition and a then value, with a default for the rest.
Book.objects.annotate(tier=Case(
When(price__gte=50, then=Value("premium")),
default=Value("standard")))Why Conditional Aggregation
The real power comes from putting Case inside an aggregate, so you can count or sum only the rows that match a condition.
Count Rows That Match
Wrap a Case in Count to tally just the matching rows, since non-matching branches return null and are skipped.
Count(Case(When(active=True, then=1)))The filter= Shortcut
Modern Django offers a cleaner way: pass filter to an aggregate to count or sum a subset without writing Case yourself.
Count("id", filter=Q(active=True))Multiple Buckets at Once
Annotate several conditional aggregates together to build a breakdown, like active and inactive counts in one query.
Team.objects.annotate(
active=Count("member", filter=Q(member__active=True)),
inactive=Count("member", filter=Q(member__active=False)))Sum a Subset
The same trick works with Sum, letting you total only the rows that pass a condition, such as paid invoices.
Sum("amount", filter=Q(status="paid"))Set an output_field
When the value types are not obvious, give Case an output_field so Django knows how to handle the result.
Case(When(...), default=Value(0), output_field=IntegerField())Order by a Case
You can even order_by a Case annotation to create custom sort priorities that plain field ordering cannot express.
One Trip to the Database
All of this branching runs server-side in a single query, far faster than looping in Python and counting by hand.
Quick Check
You want one number: how many members of a team are active, computed in the database.
Recap
Case and When add per-row branching, and pairing conditions with aggregates, or the filter= shortcut, builds rich breakdowns in one query. 📊
Frequently asked questions
Is the “Conditional Aggregation with Case/When” lesson free?
Yes — the full text of “Conditional Aggregation with Case/When” is free to read here on the web, and the Django 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 Django Academy course, upgrade to CoddyKit PRO.
What will I learn in “Conditional Aggregation with Case/When”?
Branch logic inside a single query. You practise Django 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 Django Academy?
No prior experience is required. Django 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 “Conditional Aggregation with Case/When” 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 Django Academy lesson?
Yes. Every Django 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
- aggregate vs annotate
- F Expressions for Atomic Updates
- Q Objects for Complex Filters
- Conditional Aggregation with Case/When