Search code examples
sqlaggregatepartition

Partition Over issue in SQL

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

Solution

  • 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;