CODEWITHSUNDEEP
sqlpostgresql
ark
ark

Reputation: 849

Subtracting multiple rows from two sql queries

I have created a betting database and I am trying to determine who are the best and worst betters. The easiest way I determined to do this is get the sum of the winnings by a user and subtract that by the sum amount they have bet

My first SQL query is 

SELECT fromuser,SUM(itemsgiven::int)
FROM transactions
WHERE transtype='Bet Placed'
GROUP BY fromuser
ORDER BY 2 DESC

Which returns this

fromuser    sum
15228328    2689
13896406    2634
55954103    308
37460340    64
66589399    62

Then my second query is to determine the total winnings

SELECT touser,SUM(itemsgiven::float)
FROM transactions
WHERE transtype='Bet Won'
GROUP BY touser
ORDER BY 2 DESC

which returns this

touser      sum
15228328    4387.515
55954103    152.295
13896406    120.285
66589399    71.28
37460340    56.925

My question is what is the best way to combine these queries so I can have a two columns, one with the user id and the other with their total winnings(or losings). I looked up some examples but the part that tripped me up is how do you make sure when you are subtracting the two sums that you only subtract from the same user id.

EDIT: I am using postgresql

Upvotes: 0

Views: 1722

Answers (3)

PlsqlDev
PlsqlDev

Reputation: 236

You can use UNION 

SELECT * FROM 
(SELECT fromuser as a,SUM(itemsgiven::numeric) as b
FROM transactions
WHERE transtype='Bet Placed'
GROUP BY fromuser
UNION 
SELECT touser as a,SUM(itemsgiven::numeric) as b
FROM transactions
WHERE transtype='Bet Won'
GROUP BY touser
) c
ORDER BY c.b DESC

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You could use this query:

SELECT usr, SUM(subtotal) AS total
FROM (
  SELECT   fromuser AS usr, SUM(itemsgiven::int) AS subtotal
  FROM     transactions
  WHERE    transtype='Bet Placed'
  GROUP BY fromuser

  UNION ALL

  SELECT   touser AS usr, -1*SUM(itemsgiven::float) AS subtotal
  FROM     transactions
  WHERE    transtype='Bet Won'
  GROUP BY touser
) s
GROUP BY usr
ORDER BY 2 DESC

are you sure about itemsgiven::int and then itemsgiven::float?

Upvotes: 2

Bulat
Bulat

Reputation: 6969

You can do this with UNION: 

 SELECT user_id, SUM(itemsgiven) as total_itemsgiven
 FROM
 (
   SELECT fromuser user_id, - itemsgiven::int itemsgiven
   FROM transactions
   WHERE 'Bet Placed' 
   UNION
   SELECT touser user_id, itemsgiven::int itemsgiven
   FROM transactions
   WHERE 'Bet Won'

 ) t
 GROUP BY user_id 
 ORDER BY 2 DESC

Upvotes: 0

Related Questions