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

    Question Unanswered: Using SUM!

    Hi everyone,

    I am pulling my hair out trying to figure this out. I am hoping someone can shed some light on this for me.

    I have three tables from which I am trying to pull numbers using SUM.
    I am trying to retrieve total Cost, Sell and Rebate numbers for each account manager in a given date range. So here are my tables and they link up.

    Table 1 : ORDERS - has the order info and contains the AcctMgr field.

    Table 2 : ITEMS - has the Units, Cost and Sell fields. Links to ORDERS with an OrderNo field.

    Table 3 : REBATES - has the RebateAmt field. Links to ITEMS with a SKey field.

    ORDERS has one record for every order which links to ITEMS that can have many records for one Order which links to REBATES which can have many records to link to one Item.

    The following query will yeild the correct results provided that each Item has only record(or none) in REBATES. However if there is more than one record in REBATES the results are doubled, tripled and so on.

    SELECT AcctMgr, SUM(Units * Cost) AS TotCost, SUM(Units * Sell) AS TotSell, SUM(Units * Rebates)
    WHERE ITEMS.InvDate>='Aug 1, 2003' AND ITEMS.InvDate < 'Sep 1, 2003' AND ORDERS.OrderNo=ITEMS.OrderNo AND Items.SKey*=REBATES.SKey
    GROUP BY AcctMgr
    ORDER BY AcctMgr

    Does anyone have an idea how I can get a one line per AcctMgr result set with the correct summations?

    Thanks so much for any input!

  2. #2
    Join Date
    Sep 2003
    Colombo, Sri Lanka
    If the duplicated rows are identical I sudgest you put a DISTINCT keyword after the SELECT.

    SELECT DISTINCT AcctMng .....


Posting Permissions

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