Thread: Query sum help

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    82
    Rep Power
    10

    Query sum help


    I have a database of transactions, for a given month some accounts will have two transactions (a sale and payment) and other accounts will have just one transaction (a sale). I'm trying to find the sum of all the transactions with just one transaction.

    thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,167
    Rep Power
    4274
    Originally Posted by utexas10
    I'm trying to find the sum of all the transactions with just one transaction.
    Code:
    SELECT transactions.account
         , transactions.transdate
         , transactions.amount
      FROM ( SELECT account
               FROM transactions
              GROUP
                 BY account
             HAVING COUNT(*) = 1 ) AS these
    INNER
      JOIN transactions
        ON transactions.account = these.account
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    82
    Rep Power
    10
    thanks...when I use that query and add one parameter to search only for a certain date I get a list of the transactions that match the date entered but also previous transactions from prior months, here's what i'm trying:

    Code:
    SELECT valtrans.MMNMBR
         , valtrans.TRANSDATE
         , valtrans.TRANSTOTAL
      FROM ( SELECT MMNMBR
               FROM valtrans WHERE TRANSDATE = '2013-05-28'
              GROUP
                 BY MMNMBR
             HAVING COUNT(*) = 1 ) AS these
    INNER
      JOIN valtrans
        ON valtrans.MMNMBR = these.MMNMBR
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,167
    Rep Power
    4274
    the query you wrote returns all the transactions, from all accounts, where there was only one transaction for an account on that date

    if you just want only those transcations, you have to add the date condition to the outer query as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    82
    Rep Power
    10
    got it, thanks

IMN logo majestic logo threadwatch logo seochat tools logo