Conditional Select statement in SQL query not working

1

I am trying to build a query in SQL (not my strongest skill) and I need the 'Year' column to be conditional.

The reason behind that, is that I want the functionality of my question to be such that if no date filter is selected, the data shows all values only aggregated by their location.

Currently it works when there is no date filter added, but if I do select a date, I get the following error

Screenshot 2023-01-06 at 11.05.44
Screenshot 2023-01-06 at 11.05.441018×288 23.2 KB

SQL statement

SELECT "Locations"."country_code" AS "Locations__country_code" [[, to_char(date_trunc('year', "public"."posts"."post_timestamp"), 'YYYY'),  {{post_timestamp}} AS "Year"]], sum("public"."posts"."price_paid")::float / '100' AS "Spend £", (sum("public"."posts"."price_paid") / (SELECT sum("public"."posts"."price_paid")::float FROM "public"."posts" WHERE ("public"."posts"."location_id" IS NOT NULL AND "public"."posts"."post_timestamp" IS NOT NULL [[ AND {{post_timestamp}}]])) * '100') AS "Share %"
FROM "public"."posts"
LEFT JOIN "public"."locations" "Locations" ON "public"."posts"."location_id" = "Locations"."id"
WHERE ("public"."posts"."location_id" IS NOT NULL AND "public"."posts"."post_timestamp" IS NOT NULL [[ AND {{post_timestamp}}]])
GROUP BY "Locations"."country_code", date_trunc('year', "public"."posts"."post_timestamp")
ORDER BY "Locations"."country_code" ASC, date_trunc('year', "public"."posts"."post_timestamp") ASC
2

To add to this. If I do add "public.posts.post_timestamp" to the GROUP_BY, it will work but the location are then not grouped because it's not truncating the posts to a specific year

3

Hi @ed.s
I don't know which variable type {{post_timestamp}}, but I'm guessing it's a Number, because you cannot use Field Filters like that: https://www.metabase.com/learn/sql-questions/field-filters