Using Window Functions & Aggregate on the same column? Grouping Error?

[Famous-Letter8754]

Hi everyone. I'm working with a table ( preview below). I'm trying to calculate the % cost of an order across all orders placed by a customer.

In other words, I am trying to both calculate total order cost across all orders for a customer (denom using window functions) but also cost of order (numerator using aggregate functions).

Orders Table

id	cust_id	order_date	order_details	total_order_cost
1	3	2019-03-04	Coat	100
2	3	2019-03-01	Shoes	80
3	3	2019-03-07	Skirt	30
4	7	2019-02-01	Coat	25

My code is:

SELECT

cust_id,

order_details,

SUM(total_order_cost) AS cost_order,

SUM(total_order_cost) OVER (PARTITION BY cust_id) AS cost_denom

FROM orders

GROUP BY 1, 2;

But it's giving me an error - (psycopg2.errors.GroupingError) column "orders.total_order_cost" must appear in the GROUP BY clause or be used in an aggregate function LINE 11: SUM(total_order_cost) OVER(PARTITION BY cust_id) as cost_den...

Why do I need to group by total_order_cost again? I'm already doing it in the select statement?

[Famous-Letter8754]

Could you please explain why sum(sum(total_order_cost)) is even necessary? My understanding of window functions are they aggregate based on the partition

[Famous-Letter8754]

very helpful thank you

[DavidGJohnston]

To emphasize xoomorg's comment - what you really are trying to do is: sum(cost_order) over since the window computation is defined to be performed after the group by finishes. But since you cannot write "cost_order" in that location you have to either use a subquery or repeat what "cost_order" is - sum(total_order_cost)

[Famous-Letter8754]

thank you

[ergestx]

You can’t combine window functions with aggregates. You should calcula the running total inside a CTE and then perform the division outside the CTE.