id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32155	Count() by default uses group by	Sukhinder Mann	nobody	"I am trying to use Count() with Case to find out multiple conditional counts, and count keeps using `id` as default group by. I don't want it to use `GROUP BY` by default.


{{{#!python
Staff.objects.filter(department__id__in=[2,3]).annotate(
                number_of_managers_in_UK=Sum(
                    Case(
                        When(position=""MANAGER"", location=""UK"", then=1),
                        output_field=IntegerField(),
                    )
                ),
                number_of_directors_in_USA=Sum(
                    Case(
                        When(position=""DIRECTOR"", location=""USA"", then=1),
                        output_field=IntegerField(),
                    )
                )
)
}}}


Which creates a SQL query something like this:


{{{#!sql
SELECT ""staff"".""id"",
    ""staff"".""location"",
    ""staff"".""position"",
    ""staff"".""salary"",
    ""staff"".""date_of_birth"", -- I don't want all these other attributes as well but I can't get rid of these as well, I just need the count attributes
    COUNT(
        CASE
            WHEN ""staff"".""position"" = MANAGER AND ""staff"".""location"" = UK THEN 1
            ELSE NULL
        END
    ) AS ""number_of_managers_in_UK"",
    COUNT(
        CASE
            WHEN ""staff"".""position"" = DIRECTOR AND ""staff"".""location"" = USA THEN 1
            ELSE NULL
        END
    ) AS ""number_of_directors_in_USA""
FROM ""staff""
WHERE ""staff"".""department_id"" IN (2, 3)
GROUP BY ""staff"".""id""; -- This group by is unnecessary and spoils the result set
}}}


Even though `Count` is an Aggregator function in SQL it can be used without `GROUP BY`. It would be good if we can have either a parameter or something to specify whether to use group by or not instead of defaulting to `GROUP BY`."	New feature	closed	Database layer (models, ORM)	2.2	Normal	invalid		sukhinder.mann@…	Unreviewed	0	0	0	0	0	0