Dynamic value for DATE_TRUNC not working

Hi there, I'm trying to create a simple SQL question for my users table with two filters: a date range filter which links to the createdAt (signup date) of the user and a custom text dropdown that has options for grouping the query by day, week or month. The query looks like this:

SELECT DATE_TRUNC({{group_duration}}, "public"."users"."createdAt") AS "signupDate", COUNT(*) AS "count"
FROM "public"."users"
WHERE "public"."users"."test" = false [[AND {{created_at}}]]
GROUP BY DATE_TRUNC({{group_duration}}, "public"."users"."createdAt")
ORDER BY DATE_TRUNC({{group_duration}}, "public"."users"."createdAt") ASC

Whenever I try to run the query with the group_duration filter set, I get this error:

ERROR: column "users.createdAt" must appear in the GROUP BY clause or be used in an aggregate function Position: 141

Copying the preview SQL query and running it works perfectly fine and gives me the desired result though:

Removing the group_duration filter/variable and hardcoding day, week, month also works as intended, so the issue is definitely not with the createdAt filter.

I'm unable to figure out the cause of this error (especially because the preview query works perfectly fine). Any help is appreciated! Thanks.

Try using:
Group by 1
Order by 1

1 Like

That worked perfectly! Thanks :raised_hands: