How to SUM() columns based on other column values SQL

Question

I have some data of people betting. The bets can be 'won', 'lost' or 'pending'. I want to get the amount people have bet and the amount they have won.

The amount won should be amount * odds BUT if it was a bonus bet (1) it will subtract the original stake (amount) from the total. Bonus bets do not include the original stake in the total win amount.

Also amountbet should not include the amount from Bonus bets as they are more tokens then lost money. Hopefully in one SQL.

I have got this far....

SELECT *, SUM(amount) AS amountbet 
FROM bets 
WHERE club = 2 
  AND result <> 'pending'
  AND bonus = 0
GROUP BY user

And I also have this SQL statement

SELECT *, SUM(amount * odds - (amount * bonus)) AS amountwon 
FROM bets 
WHERE club = 2 
  AND result = 'won' 
GROUP BY user

So if it is a bonus bet it will subtract amount or it will subtract amount * 0 i.e. = 0

I can probably work around if not but if there is an elegant way I would like to try.

Thanks

The DB table looks like this

ID | user | amount | odds | result | bonus | club |

MatBailie · Accepted Answer · 2021-06-21 21:54:00Z

3

You can use CASE expressions inside the SUM() to apply the filtering you want.

SELECT
  user,
  SUM(                                   amount    )  AS total_amount,
  SUM(CASE WHEN result  = 'won'     THEN amount END)  AS amount_won,
  SUM(CASE WHEN result <> 'pending' THEN amount END)  AS amount_bet
FROM
  bets 
WHERE
  club = 2 
GROUP BY
  user

There's no ELSE in those, which implicitly does ELSE NULL, and then NULLs are effectively ignored by aggregates.

answered Jun 21, 2021 at 21:54

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MomasVII Over a year ago

Sorry i completely botched this question after I ran your answer on my DB. I updated what I need. Sorry its much more confusing

MatBailie Over a year ago

@ThomasByy Just put the calculation where this answer has THEN amount; a case expression can return the result of any calculation.

Stu · Accepted Answer · 2021-06-21 21:54:52Z

0

You can use conditional aggregation using a case expression,

SELECT User,
  sum (case when result in ('won','lost') then amount end) as AmountBet,
  sum (case when result='won' then amount end) as AmountWon
FROM bets 
WHERE club = 2 
GROUP BY user

answered Jun 21, 2021 at 21:54

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to SUM() columns based on other column values SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related