Reputation: 12406
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:
price
for all of the sales made by that salesperson for that product (call this product_sales
).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?
Upvotes: 1
Views: 240
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
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