Django Conditional Statement Removes Overlap

I am attempting to use the Conditional Statements When and Case to optimize my query to count the number of instances of of people with an age less than x.

The variable b is used to reference the number of instances that I am looking to capture for that particular age via b’s length.


    age_bins = [(i) for i in range(15, 85, YEARS_PER_AGG)]
    conditionals = [When(age__lte=bin, then=Value(f"{bin}")) for bin in age_bins]
    case = Case(*conditionals, output_field=CharField())

    age_groupings = person_salaries.values("education").annotate(age_less_than=case, count=Count("id"))
    b = PersonSalary.objects.filter(Q(education='1. < HS Grad') &  Q(age__lte=25))


The length of b here is 33. This is the sum of where age_less_than is 20 and where age_less_than 25 below.

<QuerySet [{‘education’: ‘1. < HS Grad’, ‘age_less_than’: ‘20’, ‘count’: 10}, {‘education’: ‘1. < HS Grad’, ‘age_less_than’: ‘25’, ‘count’: 23}

What I am looking to create would be an output like this:

<QuerySet [{‘education’: ‘1. < HS Grad’, ‘age_less_than’: ‘20’, ‘count’: 10}, {‘education’: ‘1. < HS Grad’, ‘age_less_than’: ‘25’, ‘count’: 33}

To the best of my understanding the reason this is occurring is that each independent When statement is evaluated as as an elif rather than if.

Correct. From the docs for Case:

A Case() expression is like the ifelifelse statement in Python . Each condition in the provided When() objects is evaluated in order, until one evaluates to a truthful value. The result expression from the matching When() object is returned.

Note: This isn’t Django evaluating this. These are the semantics of the SQL CASE statement.

I believe that you would need to construct these as independent aggregates / annotations to get independent results.