I have below table where against the quantity I have Expected date of delivery. I need to find the nearest date and sum of quantity that will be shipped in.

Shipment table:-

The expected out should be

I have tried below query

SELECT
         t2.item
         t2.date,
         t1.Quantity
FROM (  SELECT
             item,
             min(EDD) as date
    FROM  table 
    GROUP BY item 
) AS  t2
LEFT JOIN(  SELECT
             item,
             min(EDD) as date,
             Quantity
    FROM  table 
    GROUP BY item,
          Quantity 
) AS  t1 ON (t2.item  = t1.item AND t2.date = t1.date)

the output I am getting is

A - 15-Jul - 20
B - 20-Jul - 10
C - 25-Jul - 5

If I add sum(Quantity) anywhere in the query the result becomes

A - 15-Jul - 60
B - 20-Jul - 20
C - 25-Jul - 12

Need help to rectify the above issue. Thanks

CodePudding user response：

Assuming table name is shipment

select item, edd, sum(quantity) from shipment 
where (item,edd) in (
  select item, min(edd) date from shipment group by item
)
group by item, edd

Second version with join (always worth to check which has better execution plan)

select s.item, s.edd, sum(s.quantity) from shipment s
join (
  select item, min(edd) min_date from shipment group by item
) m on m.item = s.item and m.min_date = s.edd
group by s.item, s.edd