Django ORM remove unwanted Group by when annotate multiple aggregate columns

Question

I want to create a query something like this in django ORM.

SELECT COUNT(CASE WHEN myCondition THEN 1 ELSE NULL end) as numyear
FROM myTable

Following is the djang ORM query i have written

year_case = Case(When(added_on__year = today.year, then=1), output_field=IntegerField())

qs = (ProfaneContent.objects
                    .annotate(numyear=Count(year_case))
                    .values('numyear'))

This is the query which is generated by django orm.

SELECT COUNT(CASE WHEN "analyzer_profanecontent"."added_on" BETWEEN 2020-01-01 00:00:00+00:00 AND 2020-12-31 23:59:59.999999+00:00 THEN 1 ELSE NULL END) AS "numyear" FROM "analyzer_profanecontent" GROUP BY "analyzer_profanecontent"."id"

All other things are good, but django places a GROUP BY at the end leading to multiple rows and incorrect answer. I don't want that at all. Right now there is just one column but i will place more such columns.

EDIT BASED ON COMMENTS I will be using the qs variable to get values of how my classifications have been made in the current year, month, week.

UPDATE On the basis of comments and answers i am getting here let me clarify. I want to do this at the database end only (obviously using Django ORM and not RAW SQL). Its a simple sql query. Doing anything at Python's end will be inefficient since the data can be too large. Thats why i want the database to get me the sum of records based on the CASE condition. I will be adding more such columns in the future so something like len() or .count will not work.

I just want to create the above mentioned query using Django ORM (without an automatically appended GROUP BY).

Answer 1

When using aggregates in annotations, django needs to have some kind of grouping, if not it defaults to primary key. So, you need to use .values() before .annotate(). Please see django docs.

But to completely remove group by you can use a static value and django is smart enough to remove it completely, so you get your result using ORM query like this:

year_case = Case(When(added_on__year = today.year, then=1), output_field=IntegerField())

qs = (ProfaneContent.objects
                    .annotate(dummy_group_by = Value(1))
                    .values('dummy_group_by')
                    .annotate(numyear=Count(year_case))
                    .values('numyear'))

Answer 2

If you need to summarize only to one row then you should to use an .aggregate() method instead of annotate().

result = ProfaneContent.objects.aggregate(
    numyear=Count(year_case),
    # ... more aggregated expressions are possible here
)

You get a simple dictionary of result columns:

>>> result
{'numyear': 7, ...}

The generated SQL query is without groups, exactly how required:

SELECT
  COUNT(CASE WHEN myCondition THEN 1 ELSE NULL end) as numyear
  -- and more possible aggregated expressions
FROM myTable