CODEWITHSUNDEEP
mysqlmysql-error-1054mysql-error-1248
Shawn
Shawn

Reputation: 11

SQL statement error

mysql> Select Emp_B AS Total
    -> From (Select Sum(mines.NoOfWorkers) AS Emp_B from mines);
ERROR 1248 (42000): Every derived table must have its own alias

mysql> Select Emp_B AS Total
    -> From (Select Sum(mines.NoOfWorkers) from mines) AS Emp_B;
ERROR 1054 (42S22): Unknown column 'Emp_B' in 'field list'

I am having some problem with this SQL statement. Any assistance will be mose appreciated

Upvotes: 1

Views: 241

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

Select temp.total  
From 
  ( Select Sum(mines.NoOfWorkers) AS total 
    from mines
  ) AS temp
;

Upvotes: 0

Suroot
Suroot

Reputation: 4423

This should work for you; however, if you're only doing this one sub select into a temp table it's kind of a waste to wrap it into another select but that's just IMHO.

Select Emp_B.sum From (Select Sum(mines.NoOfWorkers) as sum from mines) AS Emp_B;

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

Select Emp_B AS Total
From (Select Sum(mines.NoOfWorkers) AS Emp_B from mines) x;

As the error states Every derived table must have its own alias Just give it an alias, like x above. OR AS x, but the AS word is optional.

Or why alias it twice...

Select Total
From (Select Sum(mines.NoOfWorkers) AS Total from mines) x;

But since SUM gives you exactly one value, unless you have simplified the query from some larger one, this gives exactly the same result??

Select Sum(mines.NoOfWorkers) AS Total from mines;

Upvotes: 1

Related Questions