Union and COUNT()

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Bishop
Forum Newbie
Posts: 2
Joined: Thu Dec 04, 2008 4:36 pm

Union and COUNT()

Post by Bishop »

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

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';
 
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.
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Union and COUNT()

Post by novice4eva »

I am talking about getting the SQL right and not the logic inside so i have 2 solutions though personally i think solution 1 is what u want:
1:

Code: Select all

 
SELECT user_name,SUM(id)....
 
2:

Code: Select all

 
SELECT user_name,id....GROUP BY user_name,id
 
Post Reply