Partition Over issue in SQL

Question

I have a Order shipment table like below -

Order_ID	shipment_id	pkg_weight
1	101	5
1	101	5
1	101	5
1	102	3
1	102	3

I want the output table to look like below -

Order_ID	Distinct_shipment_id	total_pkg_weight
1	2	8

select 
   order_id
 , count(distinct(shipment_id)
 , avg(pkg_weight) over (partition by shipment_id)
 
from table1
group by  order_id

but getting the below error -

column "pkg_weight" must appear in the GROUP BY clause or be used in an aggregate function

Please help

What does 8 as total_pkg_weight mean? I'd say the total weight is 21. — jarlh, Feb 09 '23 at 13:08

score 0 · Accepted Answer · answered Feb 09 '23 at 12:55

Use a distinct select first, then aggregate:

SELECT Order_ID,
       COUNT(DISTINCT shipment_id) AS Distinct_shipment_id,
       SUM(pkg_weight) AS total_pkg_weight
FROM
(
    SELECT DISTINCT Order_ID, shipment_id, pkg_weight
    FROM table1
) t
GROUP BY Order_ID;

Partition Over issue in SQL

1 Answers1