Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: SQL JOIN/SUM query help

    I need help with a query that involves the joining of 3 tables. I have tables Customers, Registers and Transactions

    Customers relates to Registers on the key customer_id.
    Registers relates to Transactions on the key register_id.

    I want to return a SUM of the columns 'retail' and 'cost' from Transactions by Customer.

    I need a query something like
    SELECT, Customers.customer_id, Register.register_id, SUM(Transactions.retail) AS retail, SUM(Transactsions.cost) AS cost
    FROM (Customers <JOIN TYPE> Registers ON Customer.customer_id = Registers.customer_id) <JOIN TYPE> Transactions ON Register.register_id = Transactions.register_id
    GROUP BY, Customers.customer_id, Registers.register_id

    If there are no transaction data for a register I still want 0's to show up in the result set, ie:

    A 0001 A0001-1 35.00 15.67
    B 0002 B0001-1 0 0 <- no transactions for register for this customer

    How do I create/modify my query to give this result? I guess this is based on the join type, I've tried doin a LEFT JOIN for the join type but do not get the results I am looking for. Thanks for any help.

  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    SELECT, Customers.customer_id, Register.register_id, NVL(SUM(Transactions.retail), 0) AS retail,
    NVL(SUM(Transactsions.cost), 0) AS cost

    If you are using SQL Server 2000 then replace NVL with ISNULL.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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