DanL DanL - 5 months ago 17
MySQL Question

Sort by SUM(col) of different table - FetchAll only returns first result

I have a table of e-sporting events,

game_events
, and the total amount of sponsored/pledged money for each event can be calculated from the
cash_pledges
table.

This query works, but it only returns the first result from the
game_events
table, even when using
FetchAll()
. I would like to fetch all matching events and sort them by their
total_pot
field created from
SUM(amount)
.

$query = 'SELECT * ,
SUM(cash_pledges.amount) AS total_pot
FROM game_events
INNER JOIN cash_pledges
WHERE cash_pledges.event=game_events.id
ORDER BY total_pot DESC';
$r = $pdb->Query($query)->FetchAll();

Answer

You are missing a GROUP BY:

SELECT
    game_events.*, 
    SUM(cash_pledges.amount) AS total_pot 
FROM game_events 
    INNER JOIN cash_pledges 
WHERE cash_pledges.event=game_events.id
GROUP BY game_events.id
ORDER BY total_pot DESC

Otherwise only a single result with the sum of all cash_pledges.amounts will be returned.

Comments