[AccessD] SQL Union Query Help

Hi Paul:

Something like this works in Oracle and I presume MSSQL

(...your select statement...)
union all
(...your other select statement...)

Field count in both select statements have to match in number and in type,
at least by conversion. Like no text into number fields but the reverse will
work.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Millard, Paul
--- Sr. Developer Analyst ---WGO
Sent: Thursday, December 11, 2003 9:46 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] SQL Union Query Help


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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com