CODEWITHSUNDEEP
mysqlsqldatabase
Muhammad Haryadi Futra
Muhammad Haryadi Futra

Reputation: 33

How to solve this SQL error: Every derived table must have its own alias?

What's the matter with this SQL:

SELECT dept_name, avg_salary
FROM    (SELECT dept_name, AVG(salary) AS avg_salary                
        FROM instructor GROUP BY dept_name)
WHERE avg_salary > 42000;

I get this error:

ERROR 1248 (42000): Every derived table must have its own alias

Somebody help me, thanks.

Upvotes: 1

Views: 685

Answers (6)

Barranka
Barranka

Reputation: 21047

The error message is describing exactly what you have to do:

SELECT dept_name, avg_salary
FROM    (SELECT dept_name, AVG(salary) AS avg_salary                
        FROM instructor GROUP BY dept_name) as a
--    Add the alias HERE -------------------^^^^^
WHERE avg_salary > 42000;

If you use a subquery as a data source, you must assign an alias to it.

Upvotes: 2

Phani
Phani

Reputation: 93

For every subquery we need to give alias name,then only it will work

SELECT dept_name, avg_salary FROM (SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor GROUP BY dept_name) as samplename WHERE avg_salary > 42000;

Thank you.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

Let's break this down so you understand what this error is about and what the error message means. This way you will understand what to do the next time you get this message as well.

This part of the query is a derived table:

(SELECT dept_name, AVG(salary) AS avg_salary                
            FROM instructor GROUP BY dept_name)

That means it is acting as a table in the query. For the database engine to work properly, all tables have to have a name. That is why it needs you to add an alias.

So your query becomes:

SELECT a.dept_name, a.avg_salary
FROM    (SELECT dept_name, AVG(salary) AS avg_salary                
        FROM instructor GROUP BY dept_name) as a
WHERE a.avg_salary > 42000;

Note that I used the alias through the query and not just to name the table. This is because it is a best practice to reference all fields with the table name or alias that they are associated with. It is not necessary when you only have one table as this one or if the field name is only in one table, but it helps with maintenance (trust me you don't want to try to figure out which table a field came from when you have 12 tables joined together) and it a good habit to get into all of the time.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311143

In MySQL, every derived table (in layman's terms: a subquery) must have a table alias, even if you don't use it. Just add one, and you should be fine:

SELECT dept_name, avg_salary
FROM   (SELECT   dept_name, AVG(salary) AS avg_salary                
        FROM     instructor 
        GROUP BY dept_name) t -- t is a table alias
WHERE avg_salary > 42000;

Upvotes: 0

Rahul
Rahul

Reputation: 77866

By adding an alias like below

SELECT dept_name, avg_salary
FROM    (SELECT dept_name, AVG(salary) AS avg_salary                
        FROM instructor GROUP BY dept_name) xxx <-- Here
WHERE avg_salary > 42000;

Though you can modify your query like below

SELECT dept_name, AVG(salary) AS avg_salary 
FROM  instructor 
GROUP BY dept_name
HAVING AVG(salary) > 42000;

Upvotes: 4

jpw
jpw

Reputation: 44871

Add an alias after the derived table:

FROM    (SELECT dept_name, AVG(salary) AS avg_salary                
        FROM instructor GROUP BY dept_name) as tab

Although you could skip the derived table and use having instead:

SELECT dept_name, AVG(salary) AS avg_salary                
FROM instructor 
GROUP BY dept_name
HAVING avg_salary > 42000;

Upvotes: 1

Related Questions