Every derived table must have its own alias

[Smackie]

I have 3 tables that has same fields and when i create the query using Union all and left join I get "Every derived table must have its own alias" here is what i got right now

SELECT user_name, COUNT(id) as id, SUM(kills) as kills, SUM(wins) as wins, SUM(draws) as draws, SUM(bonus) as bonus from
(SELECT users.user_name,
	reports.id,
	reports.kills,
	reports.wins,
	reports.draws,
	reports.bonus
  FROM users LEFT JOIN reports ON users.user_name = reports.user_name
UNION all
   SELECT users.user_name,
	reports2.id,
	reports2.kills,
	reports2.wins,
	reports2.draws,
	reports2.bonus
  FROM users LEFT JOIN reports2 ON users.user_name = reports2.user_name
UNION all
   SELECT users.user_name,
	reports3.id,
	reports3.kills,
	reports3.wins,
	reports3.draws,
	reports3.bonus
  FROM users LEFT JOIN reports3 ON users.user_name = reports3.user_name)
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 5 fields each table is for a different game played.

Thank you
Smackie

[laserlight]

I believe the problem is that you failed to give a name to the derived table that you get from the UNION ALLs. The fix would be to give an arbitrary name, placing it after the closing parenthesis and before the GROUP BY clause.

Smackie wrote:

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 5 fields each table is for a different game played.

This may imply that you should have a common table consisting of these columns and a game id.

[Smackie]

Well not sure what you mean by arbitrary name but i decided to redo the query and done it like this..

$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';

It works great shows everything right except for COUNT(id).. count(id) is to get how many games the users has played.. for some reason its only counting id's from reports table and not from the other 2 tables.. now at the top if i were to use

'SELECT user_name,COUNT(id) as id,SUM(kills) as kills,SUM(wins) as wins,SUM(draws) as draws,SUM(bonus) as bonus FROM (

it would display (since i have played all 3 games. it shows 3 (which should show 97) but other ones that have only played 1 or 2 of the games shows 1 or 2 😕

[Roger_Ramjet]

and 't' is an arbitrary name just as laserlight suggested.

[tomhath]

SUM the ids that you counted:

$query = 'SELECT user_name,   SUM(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
...