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

score 1 · Answer 1 · answered Mar 27 '11 at 22:58

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;

score 1 · Answer 2 · answered Mar 27 '11 at 22:58

1

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;

answered Mar 27 '11 at 22:58

Suroot

4,315
1
22
28

score 0 · Answer 3 · answered Mar 27 '11 at 23:01

0

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

answered Mar 27 '11 at 23:01

ypercubeᵀᴹ

113,259
19
174
235

SQL statement error

3 Answers3