Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    126

    Unanswered: Sum and Count two tables

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    126
    Forgot something.. there is another column in each table named "Keycode" and they should be grouped by it

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not a temp table, but a derived table --
    Code:
    select keycode
         , count(ordernumber)
         , sum(invoiceamount)
      from ( 
              select keycode
                   , ordernumber
                   , invoiceamount
                from table1
              union all
              select keycode
                   , ordernumber
                   , invoiceamount
                from table2
           )
    group
        by keycode
    rudy
    http://r937.com/

  4. #4
    Join Date
    Jan 2003
    Posts
    126
    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.

  5. #5
    Join Date
    Jan 2003
    Posts
    126

    Got it! Thank you!

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •