SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-02-2008, 05:05 AM
| ||||
| ||||
 UNION all return two rows Dear All, I have created two select statments, each produce three lines with this layout: 1st Select: SELECT ID, Part#, Period, Value, Dis, Amt, sum(NetSaleValue) as NetSaleValue, sum(NetSaleProfit) as NetSaleProfit, 0.00 as Dum1, 0.00 as Dum2 FROM RegMaster a, Parts b Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487' and a.id = b.id AND a.part# = b.part# GROUP BY a.id, a.regCode, a.Period, a.Value, a.Dis, a.Amount 1st Output: ======== ID Part# Date Values Dis Amount NetSValue NetSProfit Dum1 Dum2 1 1487 2007/01/01 85058.16 0.00 85058.16 170116.32 7045370.52 0.00 0.00 1 1487 2007/02/01 94227.31 0.00 94227.31 188454.62 6695362.70 0.00 0.00 1 1487 2007/03/01 107397.87 0.00 107397.87 214795.74 8771624.18 0.00 0.00 ------------------------------------------------------------------------------------------------------------------------------------------------------ 2nd Select: SELECT ID, Part#, Period, Value, Dis, Amt, 0.00 as Dum1, 0.00 as Dum2, sum(NetPurchValue) as NetPurchValue, sum(NetPurchProfit) as NetPurchProfit FROM RegMaster a, Parts b Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487' and a.id = b.id AND a.part# = b.part# GROUP BY a.id, a.regCode, a.Period, a.Value, a.Dis, a.Amount 2nd Output: ======== ID Part# Date Values Dis Amount Dumm1 Dumm2 NetPValue NetPProfit 1 1487 2007/01/01 85058.16 0.00 85058.16 0.00 0.00 170116.32 7045370.52 1 1487 2007/02/01 94227.31 0.00 94227.31 0.00 0.00 188454.62 6695362.70 1 1487 2007/03/01 107397.87 0.00 107397.87 0.00 0.00 214795.74 8771624.18 ------------------------------------------------------------------------------------------------------------------------------------------------------ Now I want to combine then together and have aresult of three lines with the values of NetSaleValue, NetSaleProfit, NetPurchValue, NetPurchProfit in ONE line .... How can I acomplish that ... Thanks and best regards, Waleed      |
|
05-02-2008, 05:05 AM
| |||
| |||
| Re: UNION all return two rows On Apr 30, 2:46 am, Waleed Seada <waleed.se...@yahoo.com> wrote: > Dear All, > > I have created two select statments, each produce three lines with > this layout: > > 1st Select: > SELECT ID, > Part#, > Period, > Value, > Dis, > Amt, > sum(NetSaleValue) as NetSaleValue, > sum(NetSaleProfit) as NetSaleProfit, > 0.00 as Dum1, > 0.00 as Dum2 > FROM RegMaster a, Parts b > Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487' > and > a.id = b.id AND a.part# = b.part# > GROUP BY a.id, > a.regCode, > a.Period, > a.Value, > a.Dis, > a.Amount > > 1st Output: > ======== > ID Part# Date Values Dis Amount NetSValue NetSProfit Dum1 Dum2 > 1 1487 2007/01/01 85058.16 0.00 85058.16 170116.32 7045370.52 0.00 > 0.00 > 1 1487 2007/02/01 94227.31 0.00 94227.31 188454.62 6695362.70 0.00 > 0.00 > 1 1487 2007/03/01 107397.87 0.00 107397.87 214795.74 8771624.18 > 0.00 0.00 > > ------------------------------------------------------------------------------------------------------------------------------------------------------ > 2nd Select: > SELECT ID, > Part#, > Period, > Value, > Dis, > Amt, > 0.00 as Dum1, > 0.00 as Dum2, > sum(NetPurchValue) as NetPurchValue, > sum(NetPurchProfit) as NetPurchProfit > FROM RegMaster a, Parts b > Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487' > and > a.id = b.id AND a.part# = b.part# > GROUP BY a.id, > a.regCode, > a.Period, > a.Value, > a.Dis, > a.Amount > > 2nd Output: > ======== > ID Part# Date Values Dis Amount Dumm1 Dumm2 NetPValue NetPProfit > 1 1487 2007/01/01 85058.16 0.00 85058.16 0.00 0.00 170116.32 > 7045370.52 > 1 1487 2007/02/01 94227.31 0.00 94227.31 0.00 0.00 188454.62 > 6695362.70 > 1 1487 2007/03/01 107397.87 0.00 107397.87 0.00 0.00 214795.74 > 8771624.18 > > ------------------------------------------------------------------------------------------------------------------------------------------------------ > > Now I want to combine then together and have aresult of three lines > with the values of NetSaleValue, NetSaleProfit, NetPurchValue, > NetPurchProfit in ONE line .... > > How can I acomplish that ... > > Thanks and best regards, > Waleed Am I missing something? It appears that your FROM, WHERE, and GROUP BY clauses are identical, so you should just be able to move over the sum()s from one query to the other: SELECT ID, Part#, Period, Value, Dis, Amt, sum(NetSaleValue) as NetSaleValue, sum(NetSaleProfit) as NetSaleProfit, sum(NetPurchValue) as NetPurchValue, sum(NetPurchProfit) as NetPurchProfit 0.00 as Dum1, 0.00 as Dum2 FROM RegMaster a, Parts b Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487' and a.id = b.id AND a.part# = b.part# GROUP BY a.id, a.regCode, a.Period, a.Value, a.Dis, a.Amount |
|
05-05-2008, 05:51 AM
| ||||
| ||||
| Re: UNION all return two rows On Apr 30, 6:14*pm, b...@sybase.com wrote: > On Apr 30, 2:46 am, Waleed Seada <waleed.se...@yahoo.com> wrote: > > > > > > > Dear All, > > > I have created two select statments, each produce three lines with > > this layout: > > > 1st Select: > > *SELECT ID, > > * * * * *Part#, > > * * * * *Period, > > * * * * *Value, > > * * * * *Dis, > > * * * * *Amt, > > * * * * *sum(NetSaleValue) as NetSaleValue, > > * * * * *sum(NetSaleProfit) as NetSaleProfit, > > * * * * *0.00 *as Dum1, > > * * * * *0.00 *as Dum2 > > * * FROM RegMaster a, Parts b > > Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487' > > and > > * * * * * * * * *a.id = b.id AND a.part# = b.part# > > GROUP BY a.id, > > * * * * *a.regCode, > > * * * * *a.Period, > > * * * * *a.Value, > > * * * * *a.Dis, > > * * * * *a.Amount > > > 1st Output: > > ======== > > ID Part# * * * *Date * * Values * * * * Dis * * Amount *NetSValue * * * NetSProfit * * * * *Dum1 * * * *Dum2 > > 1 1487 *2007/01/01 85058.16 * * * * * * 0.00 * *85058.16 * * * *170116.32 * * * 7045370.52 * *0.00 > > 0.00 > > 1 1487 *2007/02/01 94227.31 * * * * * * 0.00 * *94227.31 * * * *188454.62 * * * 6695362.70 * *0.00 > > 0.00 > > 1 1487 *2007/03/01 107397.87 * *0.00 * *107397.87 * * * 214795.74 * * * 8771624.18 > > 0.00 * *0.00 > > > ---------------------------------------------------------------------------*--------------------------------------------------------------------------- > > 2nd Select: > > *SELECT ID, > > * * * * *Part#, > > * * * * *Period, > > * * * * *Value, > > * * * * *Dis, > > * * * * *Amt, > > * * * * *0.00 *as Dum1, > > * * * * *0.00 *as Dum2, > > * * * * *sum(NetPurchValue) as NetPurchValue, > > * * * * *sum(NetPurchProfit) as NetPurchProfit > > * * FROM RegMaster a, Parts b > > Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487' > > and > > * * * * * * * * *a.id = b.id AND a.part# = b.part# > > GROUP BY a.id, > > * * * * *a.regCode, > > * * * * *a.Period, > > * * * * *a.Value, > > * * * * *a.Dis, > > * * * * *a.Amount > > > 2nd Output: > > ======== > > ID Part# * * * *Date * * Values * * * * Dis * * Amount *Dumm1 * Dumm2 * NetPValue * * * NetPProfit > > 1 1487 *2007/01/01 85058.16 * * * * * * 0.00 * *85058.16 * * * *0.00 * *0.00 * *170116.32 > > 7045370.52 > > 1 1487 *2007/02/01 94227.31 * * * * * * 0.00 * *94227.31 * * * *0.00 * *0.00 * *188454.62 > > 6695362.70 > > 1 1487 *2007/03/01 107397.87 * *0.00 * *107397.87 * * * 0.00 * *0.00 * *214795.74 > > 8771624.18 > > > ---------------------------------------------------------------------------*--------------------------------------------------------------------------- > > > Now I want to combine then together and have aresult of three lines > > with the values of NetSaleValue, NetSaleProfit, NetPurchValue, > > NetPurchProfit in ONE line .... > > > How can I acomplish that ... > > > Thanks and best regards, > > Waleed > > Am I missing something? *It appears that your FROM, WHERE, and GROUP > BY clauses > are identical, so you should just be able to move over the sum()s from > one query to the other: > > * SELECT ID, > * * * * * Part#, > * * * * * Period, > * * * * * Value, > * * * * * Dis, * * * * *Amt, > * * * * * sum(NetSaleValue) as NetSaleValue, > * * * * * sum(NetSaleProfit) as NetSaleProfit, > * * * * *sum(NetPurchValue) as NetPurchValue, > * * * * *sum(NetPurchProfit) as NetPurchProfit > * * * * * 0.00 *as Dum1, > * * * * * 0.00 *as Dum2 > * * *FROM RegMaster a, Parts b > *Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487' > *and > * * * * * * * * * a.id = b.id AND a.part# = b.part# > *GROUP BY a.id, > * * * * * a.regCode, > * * * * * a.Period, > * * * * * a.Value, > * * * * * a.Dis, > * * * * * a.Amount- Hide quoted text - > > - Show quoted text - Dear All, I am sorry for the missleading ... the second select should join Regmaster with Spars b not Parts as I mention at the begining, we have three tables ... RagMaster, Parts, Spars Bets regards, Waleed |
Linear Mode
