SQL statement using sum on a self join

[gotmilk2]

I need to construct a SQL statement, against one table that has multiple versions of the same data, that would indicate if the summed quantity of the previous file quantity was different from the current file quantity.

Each row was PO# and STYLE# and QTY# and PHASE#
Example:

PO1, STYLE1, QTY1, PHASE1
PO1, STYLE2, QTY3, PHASE1
PO1, STYLE1, QTY1, PHASE2
PO1, STYLE2, QTY2, PHASE2
PO2, STYLE1, QTY1, PHASE1
PO2, STYLE2, QTY2, PHASE1
PO2, STYLE1, QTY1, PHASE2
PO2, STYLE2, QTY2, PHASE2

Only PO1 needs to return in the result set.

I tried the SQL below. I returns PO's where the summed qty is the same between the phase's.

select p1.may_po_nbr, sum(p1.ordr_qty) as p1_ordr_qty,
p2.may_po_nbr, sum(p2.ordr_qty) as p2_ordr_qty
from MC2MC_PO_INTG p1, MC2MC_PO_INTG p2
where p1.file_phase = 1
and p2.file_phase = 2
and p1.may_po_nbr = p2.may_po_nbr
and p1.ordr_qty <> p2.ordr_qty
group by p1.may_po_nbr, p2.may_po_nbr

 

[gmmastros]

Can you show some sample data (what's in the table) and also expected results? I think it will help to clarify the question.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vbkris]

hi,

i am still a bit confused. can you explain a bit more???

Known is handfull, Unknown is worldfull

 

[ESquared]

select may_po_nbr,
from mc2mc_po_intg
having
   sum(case when file_phase = 1 then ordr_qty else 0 end)
   <> sum(case when file_phase = 2 then ordr_qty else 0 end)
group by may_po_nbr