Thread: group by before and after date

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.DueDatethen sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0))
end,0) as ExpectedBefore,
coalesce(case when b.DatePromisedBy >a.DueDatethen 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

> 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.

You could :SELECT ..., sum( stuff ), ..., (DatePromisedBy > a_particular_date) as  
after GROUP BY after
You'll get two lines, one the sum of things before  a_particular_date,  
one of things after  a_particular_date. Look in the 'after' field to know  
which is which.