CODEWITHSUNDEEP
mysqlsql
Cheops
Cheops

Reputation: 71

How to fix "Every derived table must have its own alias" error in an sql query

Im trying to find the total number of goals scored by a team in a soccer matches db that I have. The db has the following fields in the results table,

Fixture_ID(PK), Home_Team, Away_Team, HTgoals, ATgoals.

When I run the following query I get the 'Every derived table must have its own alias';

SELECT SUM(goals) goals
  FROM (SELECT SUM(HTgoals) goals
          FROM `results`
          WHERE Home_team = 'Arsenal'
        UNION ALL
        SELECT SUM(ATgoals) goals
          FROM `results`
          WHERE Away_team = 'Arsenal')

How can I write the query such that the result will be the sum of all goals scored by Arsenal?

Upvotes: 0

Views: 42

Answers (4)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You have to assign an alias to the sub-select on FROM to solve the error:

SELECT SUM(goals) goals 
FROM (
    SELECT SUM(HTgoals) goals FROM `results`  WHERE Home_team = 'Arsenal' 
    UNION ALL
    SELECT SUM(ATgoals) goals FROM `results` WHERE Away_team = 'Arsenal'
) the_alias

But you can write your query in shorter way (without a sub-select or UNION ALL - and no alias):

SELECT SUM(CASE WHEN Home_team = 'Arsenal' THEN HTgoals ELSE ATgoals END) goals
FROM `results` 
WHERE Home_team = 'Arsenal' OR Away_team = 'Arsenal'

demo on dbfiddle.uk

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you have to put alias name of your subquery

 SELECT SUM(goals) goals FROM
 (SELECT SUM(HTgoals) goals FROM `results`  WHERE Home_team = 'Arsenal' 
  UNION ALL  
 SELECT SUM(ATgoals) goals FROM `results` WHERE Away_team = 'Arsenal'
 ) a --here a is alias name

but i think you dont need subquery you can try like below by using case when

select sum(sum(case when Home_team = 'Arsenal' then HTgoals else 0 end)+
       sum(case when Away_team = 'Arsenal' then ATgoals else 0 end) )
 from results

Upvotes: 0

Rahul
Rahul

Reputation: 77866

You need an alias for your subquery or derived table like

SELECT SUM(goals) goals 
FROM 
(SELECT SUM(HTgoals) goals FROM `results`  
WHERE Home_team = 'Arsenal' 
UNION ALL  
SELECT SUM(ATgoals) goals FROM `results` 
WHERE Away_team = 'Arsenal') xxx  <--- Here

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You need a column name after the FROM() Tname 

SELECT SUM(goals) goals 
FROM ( 
    SELECT SUM(HTgoals) goals 
    FROM `results`  
    WHERE Home_team = 'Arsenal'

     UNION ALL  
     SELECT SUM(ATgoals) goals 
     FROM `results` 
     WHERE Away_team = 'Arsenal')  T

Upvotes: 0

Related Questions