MYSQL every table must have its own alias error?

Question

I am trying to do a nested select query but I keep getting the same error saying that every table must have its own alias.

SELECT 
  Sum(SumOfASSIGN_HOURS) AS SumOfSumOfASSIGN_HOURS, 
  Sum(SumOfASSIGN_CHARGE) AS SumOfSumOfASSIGN_CHARGE
FROM (
  SELECT ASSIGNMENT.PROJ_NUM, SUM(ASSIGNMENT.ASSIGN_HOURS) AS SumOfASSIGN_HOURS,
  SUM(ASSIGNMENT.ASSIGN_CHARGE) AS SumOfASSIGN_CHARGE
  FROM (ASSIGNMENT)
  GROUP BY ASSIGNMENT.PROJ_NUM);

I am new to MYSQL and I cant figure out what I am doing wrong.

Have you seen this answer? https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql — Kurtis, Feb 27 '18 at 19:53
I have tried what was advised in that posting, however I still get the same error. — naauao, Feb 27 '18 at 19:59

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

The problem here is that you have created a derived table (sub-query) and it must be given alias AS aliasName or anything. Just provide an alias on your sub-query as below and you should be fine.

SELECT 
Sum(aliasName.SumOfASSIGN_HOURS) AS SumOfSumOfASSIGN_HOURS,
Sum(aliasName.SumOfASSIGN_CHARGE) AS SumOfSumOfASSIGN_CHARGE
FROM(
SELECT ASSIGNMENT.PROJ_NUM, SUM(ASSIGNMENT.ASSIGN_HOURS) AS
SumOfASSIGN_HOURS,SUM(ASSIGNMENT.ASSIGN_CHARGE) AS SumOfASSIGN_CHARGE
FROM ASSIGNMENT 
GROUP BY ASSIGNMENT.PROJ_NUM
) aliasName;

You can see more of this on the official documentation here which states that

Subqueries are legal in a SELECT statement's FROM clause. The actual syntax is:

SELECT ... FROM (subquery) [AS] name ...

The [AS] name clause is mandatory, because every table in a FROM clause must have a name. Any columns in the subquery select list must have unique names.

Yes, this answer was helpful. Thank you. – naauao Feb 27 '18 at 20:46 — naauao, Feb 27 '18 at 20:46

score 0 · Answer 2 · answered Feb 27 '18 at 20:04

From reading your query, it looks like you can skip the sub query. Your query should look like below:

SELECT PROJ_NUM, SUM(ASSIGN_HOURS) AS SumOfASSIGN_HOURS, 
SUM(ASSIGN_CHARGE) AS SumOfASSIGN_CHARGE                       
FROM ASSIGNMENT
GROUP BY PROJ_NUM

score 0 · Answer 3 · answered Feb 27 '18 at 20:06

You must add a name at the FROM ( select ..... ) Table eg add a simple alias as T

    SELECT 
    Sum(T.SumOfASSIGN_HOURS) AS SumOfSumOfASSIGN_HOURS, 
    Sum(T.SumOfASSIGN_CHARGE) AS SumOfSumOfASSIGN_CHARGE
  FROM (
    SELECT ASSIGNMENT.PROJ_NUM, SUM(ASSIGNMENT.ASSIGN_HOURS) AS SumOfASSIGN_HOURS,
    SUM(ASSIGNMENT.ASSIGN_CHARGE) AS SumOfASSIGN_CHARGE
    FROM (ASSIGNMENT)
    GROUP BY ASSIGNMENT.PROJ_NUM) T ;

MYSQL every table must have its own alias error?

3 Answers3