Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Toledo, Ohio
    Posts
    2

    Unanswered: SUM() is doubling results

    I am using a this query to get the sum of monthly statements on a website:

    SELECT DISTINCT SUM(purch_amt) AS p_purchases, SUM(sales_amt) AS p_sales, SUM(fees) AS p_fees, SUM(fees_paid) AS p_fees_paid FROM mtd_trans WHERE acct_holder='"&p_username&"' AND trans_date BETWEEN '"&begDate&"' AND '"&endDate&"'

    The query is returning results, but it is doubling the sums. Can anyone tell me what might be going on? Elsewhere on this same page, I am using another query to return all of the transactions for the month. These are displaying fine. Here is the query I am using for that:

    SELECT DISTINCT mtd_trans.auth_num, mtd_trans.acct_holder, mtd_trans.trans_date, mtd_trans.trans_with, mtd_trans.purch_amt, mtd_trans.sales_amt, mtd_trans.fees, mtd_trans.fees_paid, mtd_trans.card_num, mtd_trans.descr, members.company FROM mtd_trans INNER JOIN members ON mtd_trans.trans_with=members.username WHERE acct_holder='"&p_username&"' AND trans_date BETWEEN '"&begDate&"' AND '"&endDate&"' ORDER BY trans_date

    It is the sums from the first query that are incorrect. The recordset from the second query return all the correct transactions. However, when the transactions are added manually on a calculator, you can easily see that the sums returned from the first query are incorrect.

    Does anyone know why this might be happening? Or, has anyone seen anything similar and have a suggestion for a work around?

    Thanks in advance!

    Norma

  2. #2
    Join Date
    Feb 2004
    Location
    Toledo, Ohio
    Posts
    2

    Talking figured it out!

    I have figured it out...

    By leaving out the GROUP BY, the SUM() function is not working correctly. When I add that, it works like a charm!

Posting Permissions

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