Union and COUNT()
Posted: Thu Dec 04, 2008 4:43 pm
I have 3 tables with the same fields well they are all working correctly except for COUNT(id) which should count all the id's of each user to see how many games they have played. but for some reason its only counting the id's from reports..
here is the Query
I know what your thinking why do you have 3 tables with the same fields
answer for that is each table has different fields except for these 6 fields each table is for a different game played.
here is the Query
Code: Select all
$query = 'SELECT user_name,id,SUM(kills) as kills,SUM(wins) as wins,SUM(draws) as draws,SUM(bonus) as bonus FROM (
SELECT user_name, COUNT(id) as id, SUM(kills) as kills, SUM(wins) as wins, SUM(draws) as draws, SUM(bonus) as bonus FROM reports GROUP BY user_name
UNION all
SELECT user_name, COUNT(id) as id, SUM(kills) as kills, SUM(wins) as wins, SUM(draws) as draws, SUM(bonus) as bonus FROM reports2 GROUP BY user_name
UNION all
SELECT user_name, COUNT(id) as id, SUM(kills) as kills, SUM(wins) as wins, SUM(draws) as draws, SUM(bonus) as bonus FROM reports3 GROUP BY user_name
) t GROUP BY user_name';
answer for that is each table has different fields except for these 6 fields each table is for a different game played.