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

Question

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:

The total of price for all of the sales made by that salesperson for that product (call this product_sales).
The total of price over all of that salesperson's sales (call this total_sales).

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

See this response for a nice approach using a WITH clause. I feel like I ought to be able to do this without a subquery or WITH.
Just stumbled on this response to a different question which proposes both sum(sum(...)) and a subquery approach. Perhaps these are the best options?

Your method is the right way to do this. Get over your silliness. Window functions are very powerful and that is the right syntax with aggregation. — Gordon Linoff
– Gordon Linoff, Commented Nov 17, 2020 at 13:49
. . I was perhaps a bit harsh. I remember having exactly the same reaction once upon a time. It passed in a day or two. — Gordon Linoff
– Gordon Linoff, Commented Nov 17, 2020 at 19:37

The Impaler · Accepted Answer · 2020-11-17 03:57:44Z

1

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.

answered Nov 17, 2020 at 3:57

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

brahn Over a year ago

Good point, thank you! I still feel like I ought to be able to do this without a CTE or subquery, but your approach definitely looks more reasonable than my original code.

Fahmi · Accepted Answer · 2020-11-17 04:01:08Z

0

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

answered Nov 17, 2020 at 4:01

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related