Django conditional Subquery aggregate

You should be able to do this with a Case() expression to query the count of departments that have the type you are looking for:

from django.db.models import Case, IntegerField, Sum, When, Value

Corporation.objects.annotate(
    type_10_count=Sum(
        Case(
            When(division__department__type=10, then=Value(1)),
            default=Value(0),
            output_field=IntegerField()
        )
    )
)

I think with Subquery we can get SQL similar to one you have provided, with this code

# Get amount of departments with GROUP BY division__corporation [1]
# .order_by() will remove any ordering so we won't get additional GROUP BY columns [2]
departments = Department.objects.filter(type=10).values(
    'division__corporation'
).annotate(count=Count('id')).order_by()

# Attach departments as Subquery to Corporation by Corporation.id.
# Departments are already grouped by division__corporation
# so .values('count') will always return single row with single column - count [3]
departments_subquery = departments.filter(division__corporation=OuterRef('id'))
corporations = Corporation.objects.annotate(
    departments_of_type_10=Subquery(
        departments_subquery.values('count'), output_field=IntegerField()
    )
)

The generated SQL is

SELECT "corporation"."id", ... (other fields) ...,
  (
    SELECT COUNT("division"."id") AS "count"
    FROM "department"
    INNER JOIN "division" ON ("department"."division_id" = "division"."id") 
    WHERE (
      "department"."type" = 10 AND
      "division"."corporation_id" = ("corporation"."id")
    ) GROUP BY "division"."corporation_id"
  ) AS "departments_of_type_10"
FROM "corporation"

Some concerns here is that subquery can be slow with large tables. However, database query optimizers can be smart enough to promote subquery to OUTER JOIN, at least I've heard PostgreSQL does this.

1. GROUP BY using .values and .annotate

2. order_by() problems

3. Subquery

I like the following way of doing it:

departments = Department.objects.filter(
    type=10,
    division__corporation=OuterRef('id')
).annotate(
    count=Func('id', 'Count')
).values('count').order_by()

corporations = Corporation.objects.annotate(
    departments_of_type_10=Subquery(depatments)
)

The more details on this method you can see in this answer: https://stackoverflow.com/a/69020732/10567223