I got it. I had to put the whole case statement into the sum so my statement ended up: select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock, sum(coalesce(case when b.DatePromisedBy<=a.DueDate then coalesce(b.QuantityOrdered,0)-coalesce(b.DeliveredSum,0) end,0)) as ExpectedBefore, sum(coalesce(case when b.DatePromisedBy >a.DueDate then coalesce(b.QuantityOrdered,0)-coalesce(b.DeliveredSum,0) end,0)) as ExpectedAfter from TableA a left join TableB on a.partid=b.partid group by a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock
"Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have 2 tables 1 has a date field and component need by that date and the > other has all the upcoming orders. > I am trying to build a query that will give me the Date and ComponentNeed > and also how many components have been ordered before that date and how many > after. > PostGreSQL is telling me I need to group on DatePromisedBy. I have tried a > number of different possibilities which haven't worked and now I have run > into brain freeze. Any help would be appreciated. > > Tables > > TableA > DueDate > PartID > AmountNeeded > CurrentStock > > Table B > PartID > QuantityOrdered > DeliveredSum > DatePromisedBy > > The select that I want is > > select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock, > coalesce(case when b.DatePromisedBy<=a.DueDate > then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) > end,0) as ExpectedBefore, > coalesce(case when b.DatePromisedBy >a.DueDate > then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) > end,0) as ExpectedAfter > from TableA a > left join (Table B) on a.partid=b.partid > group by a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock > > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
