[Django]-Django ORM grouping by birthdates

Maybe it will be useful to future seekers.

Now we can achieve this question with Conditional Expressions – Case(), When() and aggregation function Count():

Also here I use relativedelta to calculate the dates.

Here is an example:

from django.utils import timezone
from django.db.models import Count, Case, When
from dateutil.relativedelta import relativedelta


current_date = timezone.now().date()

range_ages = (
    {"lookup": "gte", "label": "-17", "age": [18]},
    {"lookup": "range", "label": "18-24", "age": [18, 25]},
    {"lookup": "range", "label": "25-34", "age": [25, 35]},
    {"lookup": "range", "label": "35-44", "age": [35, 45]},
    {"lookup": "range", "label": "45-54", "age": [45, 55]},
    {"lookup": "range", "label": "55-64", "age": [55, 65]},
    {"lookup": "lt", "label": "65+", "age": [65]},
)

aggr_query = {}
for item in range_ages:
    age = item.get("age")
    lookup = item.get("lookup")
    label = item.get("label")
    # calculate start_date an end_date
    end_date = current_date - relativedelta(years=age[0])
    start_date = current_date - relativedelta(years=age[-1], days=-1)
    f_value = start_date if len(age) == 1 else (start_date, end_date)
    if lookup == "gte":
        aggr_query[label]=Count(Case(When(date_of_birth__gte=f_value, then=1)))
    elif lookup == "lt":
        aggr_query[label]=Count(Case(When(date_of_birth__lt=f_value, then=1)))
    else:
        aggr_query[label]=Count(Case(When(date_of_birth__range=f_value, then=1)))

#Aggregate values
qs_values = MyModel.objects.filter(gender=gender).aggregate(**aggr_query)

The output will be like this:

{'55-64': 1726, '25-34': 2590, '65+': 5691, '18-24': 517, '45-54': 1209, '-17': 0, '35-44': 2416}

We can also use annotate() it returns us an QuerySet of objects.

Maybe try something like this (not tested):

case_when_query = "(case when extract...end)" # Your case when query here

extra_qs = Reader.objects.extra(select={'count': 'count(1)', 'age': case_when_query})

query_set = extra_qs.values('count', 'age')
query_set.query.group_by = ['age']