SQL filter widgets Must appear in the GROUP BY clause or be used in an aggregate function-CodePudding

I'm trying to create filter widgets on metabase. I'm having an issue with my {{breakdown}} filter, when I put in 'year' instead of {{breakdown}} it works perfectly giving me a yearly breakdown report. But when I use {{breakdown}} and then input year into the widget I get the error:

column "analytics_moduleattemptanalyticsview.created" must appear in the GROUP BY clause or be used in an aggregate function Position: 27

does anyone know how to fix this? I want this as a widget so that I can see month and quarters too

 SELECT date_trunc({{breakdown}}, CAST("public"."analytics_moduleattemptanalyticsview"."created" AS timestamp)) AS "created", 
    "public"."analytics_moduleattemptanalyticsview"."is_complete" AS "is_complete", 
    count(*) AS "count"
    FROM "public"."analytics_moduleattemptanalyticsview"
    LEFT JOIN "public"."auth_user" "User" ON "public"."analytics_moduleattemptanalyticsview"."user_id" = "User"."id" 
    LEFT JOIN "public"."companies_company" "Companies Company" ON ((floor((("public"."analytics_moduleattemptanalyticsview"."user_company_id" - 0.0) / 2000)) * 2000)   0.0) = "Companies Company"."id"
    WHERE "User"."is_staff" = FALSE
    [[and {{company_id}} and {{company_type}} and {{platform}}]]
    GROUP BY date_trunc({{breakdown}}, CAST("public"."analytics_moduleattemptanalyticsview"."created" AS timestamp)), "public"."analytics_moduleattemptanalyticsview"."is_complete"
    ORDER BY date_trunc({{breakdown}}, CAST("public"."analytics_moduleattemptanalyticsview"."created" AS timestamp)) ASC, "public"."analytics_moduleattemptanalyticsview"."is_complete" ASC

CodePudding user response：

You can write this query very simple and readable:

SELECT 
    date_trunc({{breakdown}} as brk_down, 
    CAST(t1."created" AS timestamp)) AS created, 
    t1.is_complete, 
    count(*) AS counts 
FROM "public"."analytics_moduleattemptanalyticsview" t1
LEFT JOIN "public"."auth_user" us ON t1.user_id = us.id 
LEFT JOIN "public"."companies_company" cc ON ((floor(((t1."user_company_id" - 0.0) / 2000)) * 2000)   0.0) = cc.id
WHERE us.is_staff = FALSE
[[and {{company_id}} and {{company_type}} and {{platform}}]]
GROUP BY brk_down, created, t1.is_complete
ORDER BY brk_down, created ASC, t1.is_complete asc