Where should the GROUP BY be placed?

Question

I'm navigating a code base and I came across some raw SQL as shown below

    SELECT count(1) AS "n", "forms_newspaperperson"."sex", "forms_newspapersheet"."topic" 
    FROM "forms_newspaperperson" 
    INNER JOIN "forms_newspapersheet" ON ("forms_newspaperperson"."newspaper_sheet_id" = "forms_newspapersheet"."id")
     , "reports_weights" 
    WHERE ("forms_newspaperperson"."deleted" = %s 
    AND "forms_newspapersheet"."country" IN (%s) 
    AND "forms_newspaperperson"."sex" IN (%s, %s) 
    AND (reports_weights.country = forms_newspapersheet.country) 
    AND (reports_weights.media_type = 'Print'))`

this raises the following error

django.db.utils.ProgrammingError: column "forms_newspaperperson.sex" must appear in the GROUP BY clause or be used in an aggregate function
web_1  | LINE 1: SELECT count(1) AS "n", "forms_newspaperperson"."sex", "form...

any suggestions on where I should place the GROUP BY?

Answer 1

You have an aggregation query. So you need a group by:

SELECT count(1) AS "n", npp."sex", nps."topic"
FROM "forms_newspaperperson" npp INNER JOIN
     "forms_newspapersheet" nps
     ON npp."newspaper_sheet_id" = nps."id" CROSS JOIN
     "reports_weights" w
     ON w.country = npp.country
WHERE npp."deleted" = %s AND nps."country" IN (%s) AND
      npp."sex" IN (%s, %s) AND w.media_type = 'Print'
GROUP BY npp."sex", nps."topic";

Also note that I fixed the FROM clause to remove the comma -- which is antiquated -- and replace it with proper, explicit, standard, readable JOIN syntax.