Variable parsing in a Native Query


#1

Hi,

new (and very exited!) user of Metabase and I have a question: I’m trying to add the following query to a new Question but it seems that the sql parser is “too smart” for it to work (database is PostgreSQL by the way):

select to_char(created_at, {{granularity}}) as time, avg(rating) as avg_rating, count(*) as ratings
from table1 a 
where id = {{id}}
group by to_char(a.created_at, {{granularity}})
order by time

The idea is to pass “YYYYMM” or “YYYYMMDD” as the Granularity parameter in order for the user to select if they want to group the data by Month or by Day, but it doesn’t seem to work. The error is:

org.postgresql.util.PSQLException: ERROR: column "a.created_at" must appear in the GROUP BY clause or be used in an aggregate function Position: 134

Do you guys have any idea how to achieve this?


#2

I tried to do something similar in SQL Server. Failed too.
Given that there are a limited number of likely values, can you do something using CASE (or a PostgreSQL equivalent)?


#3

Amazing! Not sure I would have come up with this by myself :slight_smile:

with cte as (
    select case 
        when {{granularity}} = 'YYYYMM' THEN to_char(a.created_at, 'YYYYMM')
        when {{granularity}} = 'YYYYWW' THEN to_char(a.created_at, 'YYYYWW')
        when {{granularity}} = 'YYYYMMDD' THEN to_char(a.created_at, 'YYYYMMDD')
        else to_char(a.created_at, 'YYYYMM')
        end as time_granularity, 
        rating
    from table1 a 
    where id = {{id}}
)
select time_granularity, avg(rating) as avg_rating, count(*) as ratings
from cte
group by time_granularity
order by time_granularity

This works without a hitch :smiley: