DanL DanL - 5 months ago 18
SQL Question

Retrieving quotient of two SUM(col) JOIN ON different values

Sorry for confusing title, didn't know how to word this.

I have a table of events, and a table of bets on those events. I'm already fetching the total amount bet on each event, but I also want to fetch the odds (aka, the quotient of the total for one team divided by the total of the other team). The participant being bet on is stored in the

horse
column of the
data_bets
table.

For instance, I want to add something like:

SELECT
SUM(data_bets.amount) WHERE data_bets.horse = data_events.challenger
/
SUM(data_bets.amount) WHERE data_bets.horse = data_events.contestant
AS bet_odds


to my existing query below:

SELECT *,
SUM(data_bets.amount) AS total_pot,
COUNT(data_bets.member) AS total_bets,
COUNT(data_votes.member) AS total_votes,
data_platforms.name AS platform_name, data_platforms.icon AS platform_icon
FROM data_events
LEFT JOIN data_bets ON data_bets.event=data_events.id
LEFT JOIN data_votes ON data_votes.content_type=2 AND data_votes.content_id=data_events.id
LEFT JOIN data_platforms ON data_platforms.id=data_events.platform
WHERE status=1
GROUP BY data_events.id
ORDER BY total_pot DESC


Is this possible to do with one query? If possible, I'd prefer to fetch both
challenger_odds
and
contestant_odds
, like this:

SELECT
SUM(data_bets.amount) WHERE data_bets.horse = data_events.challenger
/
SUM(data_bets.amount) WHERE data_bets.horse = data_events.contestant
AS challenger_odds,
SUM(data_bets.amount) WHERE data_bets.horse = data_events.contestant
/
SUM(data_bets.amount) WHERE data_bets.horse = data_events.challenger
AS contestant_odds

Answer

Use conditional aggregation:

SELECT (SUM(CASE WHEN data_bets.horse = data_events.challenger THEN data_bets.amount ELSE 0 END)  /
        SUM(CASE WHEN data_bets.horse = data_events.contestant THEN data_bets.amount END) 
       ) as bet_odds
Comments