1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Development Combining SQL queries

Discussion in 'Software' started by Firehed, 13 Jun 2009.

  1. Firehed

    Firehed Why not? I own a domain to match.

    Joined:
    15 Feb 2004
    Posts:
    12,574
    Likes Received:
    16
    Hey all,

    Haven't been around much recently as I've been quite busy with a startup, but I've got a quick SQL question. I've basically got a transactions table in my DB set up in the way you'd probably more or less expect: id, amount, sender_id, recipient_id, and some other fields that are quite irrelevant to the discussion.

    Is there a good way to calculate an account's total transaction history in one fell swoop? It's easy enough to do two "SELECT sum(amount) as sentTotal FROM transactions WHERE sender_id = X" type of queries and combine the values externally; just wondering if there's a "cleaner" way to do it.

    Thanks!
     
  2. Hamish

    Hamish New Member

    Joined:
    25 Nov 2002
    Posts:
    3,649
    Likes Received:
    4
    so you want the sum of account where the user is the sender and another sum where they're the recipient?

    2 sub-queries?
    self-join?
     
  3. VipersGratitude

    VipersGratitude Well-Known Member

    Joined:
    4 Mar 2008
    Posts:
    2,977
    Likes Received:
    378
    WHERE
    sender_id = "X" OR reciever_id="X"
     
  4. Firehed

    Firehed Why not? I own a domain to match.

    Joined:
    15 Feb 2004
    Posts:
    12,574
    Likes Received:
    16
    The value has to be subtracted in one case and added in the other.
     
  5. yakyb

    yakyb i hate the person above me

    Joined:
    10 Oct 2006
    Posts:
    2,064
    Likes Received:
    36
    Something like the following would probably do


    Code:
    select isnull( S.sender_id , receiver_id ) as ID, sentTotal, receivedTotal , isnull(receivedtotal,0) - isnull(senttotal,0) as [ACCOUNT TOTAL]
    from (
    SELECT  sender_id, sum(amount) as sentTotal FROM transactions 
    group by sender_id
    ) S outer join (
    SELECT reciever_id, sum(amount) as receivedTotal FROM transactions 
    GROUP BY RECEIVER_ID) R on S.sender_id = R.receiver_id
    
     
  6. Daedelus

    Daedelus New Member

    Joined:
    7 May 2009
    Posts:
    253
    Likes Received:
    12
    This might work - I can't actually test this, I've just written it 'blind' and it's probably inefficient.
    (I've assumed your table is called 'transactions')

    Code:
    SELECT account_id, total1.total, total2.total FROM
    	( SELECT DISTINCT sender_id AS account_id FROM transactions t1 UNION ALL SELECT recipient_id AS account_id FROM transactions t2 ) as account_id a 
    	LEFT OUTER JOIN	(SELECT sender_id, SUM(amount) as total FROM transactions GROUP BY sender_id) as total1 ON total1.sender_id = a.account
    	LEFT OUTER JOIN (SELECT recipient_id, SUM(amount) as total FROM transactions GROUP BY recipient_id) as total2 ON total2.recipient_id = a.account_id
     

Share This Page