SQL Union Query Help

[mrpauly]

Hello,

I'm trying to run the SQL union query and would like to have this merged into one record...Right now, the results are displayed into two records.

Eventually, I will be removing the Customer Number where clause to run against all customers.

Thanks,
Paul


select
t1.ShipNum as CustNum,
Count(*) as Shipments,
Sum(t1.Rev) as Revenue,
Sum(t1.AdjCost) as AdjCost
from
tblRawAccess as t1
where
t1.ShipNum = 'KKL2712SKM' and
t1.FbType = 'P' and
t1.Month = '0306'
group by
t1.ShipNum
union
select
t1.CsgnNum as CustNum,
Count(*) as Shipments,
Sum(t1.Rev) as Revenue,
Sum(t1.AdjCost) as AdjCost
from
tblRawAccess as t1
where
t1.CsgnNum = 'KKL2712SKM' and
t1.FbType = 'C' and
t1.Month = '0306'
group by
t1.CsgnNum

 

[swampBoogie]

select
case when t1.fbtype = 'P' then t1.ShipNum 
     else t1.CsgnNum end as CustNum,
Count(*) as Shipments,
Sum(t1.Rev) as Revenue,
Sum(t1.AdjCost) as AdjCost
from
tblRawAccess as t1
where
t1.ShipNum = 'KKL2712SKM' and
t1.FbType in ('C','P') and
t1.Month = '0306'
group by
case when t1.fbtype = 'P' then t1.ShipNum 
     else t1.CsgnNum end

 

[r937]

yes, of course there are two records, unless by sheer coincidence you get two rows with exactly the same Shipments and Revenue and AdjCost -- in that case, one of them will be dropped!!

in order to merge them into one record, you need to do two things: replace UNION with UNION ALL, and then select from the derived table produced by union

like this --

[tt]select CustNum
, sum(Shipments) as totalShipments
, Sum(Revenuev) as totalRevenue
, Sum(AdjCost) as totalAdjCost
from (
select t1.ShipNum as CustNum
, Count(*) as Shipments
, Sum(t1.Rev) as Revenue
, Sum(t1.AdjCost) as AdjCost
from tblRawAccess as t1
where t1.ShipNum = 'KKL2712SKM'
and t1.FbType = 'P'
and t1.Month = '0306'
group
by t1.ShipNum
union all
select t1.CsgnNum
, Count(*)
, Sum(t1.Rev)
, Sum(t1.AdjCost)
from tblRawAccess as t1
where t1.CsgnNum = 'KKL2712SKM'
and t1.FbType = 'C'
and t1.Month = '0306'
group
by t1.CsgnNum
) as derived table
group
by CustNum[/tt]

on the other hand, i think you can also accomplish the same results with this --

[tt]select t1.ShipNum as CustNum
, Count(*) as Shipments
, Sum(t1.Rev) as Revenue
, Sum(t1.AdjCost) as AdjCost
from tblRawAccess as t1
where t1.ShipNum = 'KKL2712SKM'
and t1.FbType in ('P','C')
and t1.Month = '0306'
group
by t1.ShipNum[/tt]

the reason that works too is because the two parts of the union were collapsed into the single FbType in ('P','C') consition (not all unions can be collapsed this way)

rudy

http://r937.com/

 

[r937]

my bad -- my first query works, but not my second

i missed the fact that the first subquery uses ShipNum and the second uses CsgnNum

swampboogie's query handles that nicely


rudy

 

[mrpauly]

Thanks Rudy and Swampboogie for your time and efforts. Yeah, the first query worked well.

Thanks,
Paul