SQL statement error

Question

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

Answer 1

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;

Answer 2

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;

Answer 3

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