brahn
brahn

Reputation: 12406

how to avoid sum(sum()) when writing this postgres query with window functions?

Runnable query example at https://www.db-fiddle.com/f/ssrpQyyajYdZkkkAJBaYUp/0

I have a postgres table of sales; each row has a sale_id, product_id, salesperson, and price.

I want to write a query that returns, for each (salesperson, product_id) tuple with at least one sale:

My current query is as follows, but I feel silly writing sum(sum(price)). Is there a more standard/idiomatic approach?

select 
  salesperson,
  product_id,
  sum(price) as product_sales,
  sum(sum(price)) over (partition by salesperson) as total_sales
from sales
group by 1, 2
order by 1, 2

Writing sum(price) instead of sum(sum(price)) yields the following error:

column "sales.price" must appear in the GROUP BY clause or be used in an aggregate function

UPDATES

Upvotes: 1

Views: 240

Answers (2)

Fahmi
Fahmi

Reputation: 37483

You can try the below -

select * from
(
select 
  salesperson,
  product_id,
  sum(price) over(partition by salesperson,product_id) as product_sales,
  sum(price) over(partition by salesperson) as total_sales,
  row_number() over(partition by salesperson,product_id order by sale_id) as rn
from sales s
)A where rn=1

Upvotes: 0

The Impaler
The Impaler

Reputation: 48865

You can use a Common Table Expression to simplify the query and do it in two steps.

For example:

with 
s as (
  select 
    salesperson,
    product_id,
    sum(price) as product_sales
  from sales
  group by salesperson, product_id
)
select
  salesperson, 
  product_id,
  product_sales,
  sum(product_sales) over (partition by salesperson) as total_sales
from s
order by salesperson, product_id

Result:

 salesperson  product_id  product_sales  total_sales 
 ------------ ----------- -------------- ----------- 
 Alice        1           2000           5400        
 Alice        2           2200           5400        
 Alice        3           1200           5400        
 Bobby        1           2000           4300        
 Bobby        2           1100           4300        
 Bobby        3           1200           4300        
 Chuck        1           2000           4300        
 Chuck        2           1100           4300        
 Chuck        3           1200           4300        

See running example at DB Fiddle.

Upvotes: 1

Related Questions