[AccessD] SQL Union Query Help

Millard, Paul --- Sr. Developer Analyst ---WGO Paul.Millard at freight.fedex.com
Thu Dec 11 11:45:41 CST 2003


Hello everyone.

I'm trying to run the SQL union query and would like to have this shown into one record...Right now, the results are displayed into two records.  This query is run against our SQL Server database.  Also, in Oracle I'm able to use a SQL wrapper which helps my situation but won't work in SQL Server...For example, select CustNum, Shipments, Revenue, AdjCost from(...SQL query below).

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

Thanks,
Paul Millard
San Jose, CA


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

query results....
KKL2712SKM	1	72.910003662109375	129.64300537109375
KKL2712SKM	29	4155.4299850463867	3413.1434745788574

need into 
KKL2712SKM 30	4228			3543



**********************************************************
This message contains information that is confidential
and proprietary to FedEx Freight or its affiliates.
It is intended only for the recipient named and for
the express purpose(s) described therein.
Any other use is prohibited.
****************************************************************



More information about the AccessD mailing list