Variable in date_trunc causes error

1

I am trying to add a custom time period for the date_trunc SQL function ('week', 'month' etc) but as soon as I remove the string 'month' and add the variable (even if I have 'month' in the input) it causes the following error

ERROR: column "source.created_at" must appear in the GROUP BY clause or be used in an aggregate function Position: 22

DB is postgreql. This was a working question that I converted to SQL to add these variables

SELECT date_trunc({{duration}}, "source"."created_at")
2

Hi @ed.s
Are you sure you're not changing other things, when replacing the string with the variable?
Try posting your entire query.
Alternatively, you can try something like this, where you use a case:
https://github.com/metabase/metabase/issues/6583#issuecomment-541742278

3

original code

SELECT date_trunc('month', "source"."created_at") AS "created_at", avg("source"."story_views_eng") AS "avg"
FROM (SELECT "public"."posts"."story_views" AS "story_views", ((CAST("public"."posts"."story_views" AS float) / CASE WHEN "public"."posts"."following" = 0 THEN NULL ELSE "public"."posts"."following" END) * 100) AS "story_views_eng", "public"."posts"."created_at" AS "created_at" FROM "public"."posts") "source"
WHERE "source"."story_views" > 0
GROUP BY date_trunc('month', "source"."created_at")
ORDER BY date_trunc('month', "source"."created_at") ASC

Adjusted

SELECT date_trunc({{Duration}}, "source"."created_at") AS "created_at", avg("source"."story_views_eng") AS "avg"
FROM (SELECT "public"."posts"."story_views" AS "story_views", ((CAST("public"."posts"."story_views" AS float) / CASE WHEN "public"."posts"."following" = 0 THEN NULL ELSE "public"."posts"."following" END) * 100) AS "story_views_eng", "public"."posts"."created_at" AS "created_at" FROM "public"."posts") "source"
WHERE "source"."story_views" > 0
GROUP BY date_trunc('month', "source"."created_at")
ORDER BY date_trunc('month', "source"."created_at") ASC

I've also tried adding the same variable to the group_by and order_by sections in case they needed to be changed together but I get the same errors

4

@ed.s But you also need to change your date_trunc('month', or date_trunc({{Duration}}, since those needs to match.
Remember that the variables automatically includes quoting, so you should not try to type that in the filter widget.

5

Same problem. Had tried this before but to no avail.

SELECT date_trunc({{Duration}}, "source"."created_at") AS "created_at", avg("source"."story_views_eng") AS "avg"
FROM (SELECT "public"."posts"."story_views" AS "story_views", ((CAST("public"."posts"."story_views" AS float) / CASE WHEN "public"."posts"."following" = 0 THEN NULL ELSE "public"."posts"."following" END) * 100) AS "story_views_eng", "public"."posts"."created_at" AS "created_at" FROM "public"."posts") "source"
WHERE "source"."story_views" > 0
GROUP BY date_trunc({{Duration}}, "source"."created_at")
ORDER BY date_trunc({{Duration}}, "source"."created_at") ASC

25
251192×572 26.6 KB

6

and for clarity I am not adding additional quotation marks either

7

@ed.s Okay, so this is caused by some weird query planner in Postgres.
You can either do this:

SELECT date_trunc({{Duration}}, "source"."created_at") AS "created_at", avg("source"."story_views_eng") AS "avg"
FROM (SELECT "public"."posts"."story_views" AS "story_views", ((CAST("public"."posts"."story_views" AS float) / CASE WHEN "public"."posts"."following" = 0 THEN NULL ELSE "public"."posts"."following" END) * 100) AS "story_views_eng", "public"."posts"."created_at" AS "created_at" FROM "public"."posts") "source"
WHERE "source"."story_views" > 0
GROUP BY 1
ORDER BY 1 ASC

Or this:

SELECT date_trunc({{Duration}}, "source"."created_at") AS "my_aliased_created_at", avg("source"."story_views_eng") AS "avg"
FROM (SELECT "public"."posts"."story_views" AS "story_views", ((CAST("public"."posts"."story_views" AS float) / CASE WHEN "public"."posts"."following" = 0 THEN NULL ELSE "public"."posts"."following" END) * 100) AS "story_views_eng", "public"."posts"."created_at" AS "created_at" FROM "public"."posts") "source"
WHERE "source"."story_views" > 0
GROUP BY "my_aliased_created_at"
ORDER BY "my_aliased_created_at" ASC
8

Thank you for this. The first option works beautifully!
There is an unintended consequence in that it's removed the ability to filter by date now... i am guessing this is because it's been converted to an sql query that it's not allowing the automatic application of date filtering? Or is there a way to reintroduce it?

9

@ed.s Add a variable inside of the sub-select to filter the period, example Field Filter connected to posts.created_at in the sidebar:
FROM "public"."posts" WHERE {{fieldfilter}}) "source"
More examples:
https://www.metabase.com/learn/sql-questions/field-filters

10

Superstar! Works brilliantly now. The flexibility of this tool is amazing... second only to the support :+1: