CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Arsène Gishyan
Arsène Gishyan

Reputation: 1

Window function after aggregation

I'm making a report, which includes window function. I have to sum values by row and show total by manufacturer, but I get different sums:

select manufacturer, 
  product_code,
  date_code,
  sum(packs),
  sum(packs) over(partition by manufacturer,date_code)
from staging.sales
group by manufacturer, 
  product_code,
  date_code, 
  packs

I assume, it's due to grouping packs, but without it I get error. How do I solve this?

Thanks in advance!

Upvotes: 0

Views: 132

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Use sum(sum(packs)):

select manufacturer, product_code, date_code,
       sum(packs),
       sum(sum(packs)) over (partition by manufacturer) as manufacturer_sum
from staging.sales
group by manufacturer, product_code, date_code;

However, if you just need the total by manufacturer, perhaps you don't need window functions at all

select manufacturer, sum(packs) as manufacturer_sum
from staging.sales
group by manufacturer;

Upvotes: 1

Related Questions