[SOLVED] SUM and COUNT, 2 tables

[jaymc]

This works

SELECT count(*)
FROM arcade_games
GROUP BY champion

 

When I add a SUM in, for another table using a join, COUNT displays the wrong result (the correct result x amount of row matches in arcade_games)

SELECT count(*), SUM(arcade_games.plays)
FROM arcade_games
LEFT JOIN arcade_plays ON arcade_games.champion = arcade_plays.player
GROUP BY champion

 

 

Any ideas?

[amites]

try assigning count to an individual field, otherwise no telling what it's going to be counting

[jaymc]

I had that originally, that did not work either

[fenway]

Did you try count(arcade_games.champion)?

[jaymc]

Did you try count(arcade_games.champion)?

Yes, it really does not seem to like it

 

Perhaps its an illegal operation?

 

I tried to do something pretty similar a while back and you guys said it was an illegal operation

[fenway]

Sorry, I just re-read the query... COUNT() counts every _matching_ row it encounters in _any_ table, hence MxN.

 

How about this? (very untested)

 

SELECT count(*), SUM (sub.plays)
FROM (
SELECT champion, arcade_games.plays
FROM arcade_games
LEFT JOIN arcade_plays ON arcade_games.champion = arcade_plays.player ) AS sub
GROUP BY sub.champion

[Barand]

SELECT count(DISTINCT arcade_games.champion), SUM(arcade_games.plays)

FROM arcade_games

LEFT JOIN arcade_plays ON arcade_games.champion = arcade_plays.player

GROUP BY champion

[jaymc]

Great, thanks

 

I actually ended up using a cron job to generate the results and store them in a cache table, which I suppose is better for load

 

However, the syntax above will come in handy for future queries!!

[fenway]

SELECT count(DISTINCT arcade_games.champion), SUM(arcade_games.plays)

FROM arcade_games

LEFT JOIN arcade_plays ON arcade_games.champion = arcade_plays.player

GROUP BY champion

Interesting use of DISTINCT... never thought of that.