Home > Enterprise >  How to group by month in presto SQL
How to group by month in presto SQL

Time:10-19

I am trying to group by per month in Presto SQL. I tried this:

select
    date_trunc('month', CAST(date AS date)) date_month,
    sum(gross_revenue,0) AS 'monthly_net_revenue'
    from gross_revenue_calculator
    group by date_trunc('month', date)

This gives me the following error: Malformed query: line 61:27: mismatched input ''monthly_net_revenue''. Expecting: <identifier>

Expected output:

October:  $102.12
November: $90.12

CodePudding user response:

You should not use single quotes as column name it either no quotes or double, also you can reference columns by index in GROUP BY as you do in your WITH clause:

select
    date_trunc('month', CAST(date AS date)) date_month,
    sum(gross_revenue,0) AS monthly_net_revenue
from gross_revenue_calculator
group by 1
  • Related