SQL window function is grouped, but still get "must be an aggregate expression or appear in GROUP BY clause"

Question

I have a SQL (presto) query, let's say it's this:

select
      id
    , product_name
    , product_type
    , sum(sales) as total_sales
    , sum(sales) over (partition by type) as sales_by_type

from some_table

group by 1,2,3

When I run this, I get an error telling me that the window function needs to appear in the GROUP BY clause. Is the best solution to break this out with a subquery? Or is there some syntax changes I need to make for this to work?

Answer 1

What you need is something like below

select
      id
    , product_name
    , product_type
    , sum(sales) over () as total_sales
    , sum(sales) over (partition by type) as sales_by_type
    from some_table

or

select
      id
    , product_name
    , product_type
    , sum(sales) over (partition by (select 1)) as total_sales
    , sum(sales) over (partition by type) as sales_by_type
    from some_table

Both of these works in sql server. Not sure what/if it will work for presto though.

I have seen below variation as well.

over (partition by null)