Hi ya,

I am having a time coming up with the correct sql statement to sum and count two fields. I am using sql 2000.

I have two tables table1 and table2 they both have the same column names, but the data is different, table1 has open orders and table2 has shipped orders.

table: table1
Column: OrderNumber, InvoiceAmount
Data: 100, 110
Data: 2021, 120

table: table2
Column: OrderNumber, InvoiceAmount
Data: 1230, 130
Data: 2233, 140

I need to count all the "OrderNumber" , and sum all the "InvoiceAmount" from both tables.

My result should look like: OrderCount: 4 InvoiceTotal: 500

I would like to do it without making a temp table as there is 1.2 million rows in table1

Thanks for any help!

Ken

Forgot something.. there is another column in each table named "Keycode" and they should be grouped by it

not a temp table, but a derived table --rudy
http://r937.com/

Than you for your reply, I'm still haveing a bit of trouble though...

This is the statement I tried:

select KEYCODE,
count([Order Number])as Orders,
sum([Invoice Total Shipped])as AmountTotal
from
(select keycode,
[Order Number],
[Invoice Total Shipped]
from DW_BILLINGHEADER
union all
select keycode,
[Order Number],
[Invoice Total Shipped]
from DW_ORDERHEADER)

group by Keycode

and I get:

Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'group'.

SQL2000 isn't likeing the selects after the "From"

I know the union works, because I ran that by itself and it works.

This Statment Worked!

SELECT keycode, COUNT([Order Number]) AS Orders, SUM([Invoice Total Shipped]) AS AmountTotal
FROM (SELECT keycode, [Order Number], [Invoice Total Shipped]
FROM DW_BILLINGHEADER
UNION ALL
SELECT keycode, [Order Number], [Invoice Total Shipped]
FROM DW_ORDERHEADER) DERIVEDTBL
GROUP BY keycode

DERIVEDTBL was missing...

Thanks So much,

Ken