Every derived table must have its own alias[Solved]

In this article, we will be discussing the below points

Let’s get started, but first, we will look into the data we will be using in our examples. Assume that we have a table sale_details with the below rows.

figure 1.1

HOW TO FIX THE ERROR in MySQL : Every derived table must have its own alias

What is a derived table?  Well, the Derived table is an expression that creates a temporary table in the FROM clause’s scope in the query. It is a subquery in the SELECT statement and FROM clause. Let’s look into an example to get the average of the maximum no_products_sold for each salesperson for a department. Observe the below query.

 SELECT 
    CEIL(AVG(max_sales))
FROM
    (SELECT 
        sale_person_name, MAX(no_products_sold) AS max_sales
    FROM
        sale_details
    GROUP BY sale_person_name);

In the above query, we created a derived table from subquery “SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name,” which is within the scope of the FROM clause.

On running the query written above to get the average of the maximum no_products_sold for each salesperson per department, we get an ERROR :

Action Output Message: SELECT CEIL(AVG(max_sales)) FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name) LIMIT 0, 1000 Error Code: 1248. Every derived table must have its own alias 0.00025 sec

It is highlighting that an Error:1248 Every derived table must have its alias has occurred. The reason for the same is that we need to provide an alias for the sub-queries while working in MySQL (alias is just a temporary/false name). Observe the corrected query below:

 SELECT 
    CEIL(AVG(max_sales)) AS average_of_max_sales_per_salesman
FROM
    (SELECT 
        sale_person_name, MAX(no_products_sold) AS max_sales
    FROM
        sale_details
    GROUP BY sale_person_name) AS sales_alias;   

In the above query, we have added an alias sales_alias for the derived table. On running the query now, we get the desired result.

Action Output Message: SELECT CEIL(AVG(max_sales)) FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name)as sales_alias LIMIT 0, 1000 1 row(s) returned 0.00086 sec / 0.000018 sec.

Output :-

figure 1.2

Every derived table must have its own alias : JOIN

Let’s see how to work with joins while using the derived tables. We will be using the same table sale_details to get all the columns for a salesperson corresponding to the row, which shows the maximum sale for a product department wise. We need to do an inner join of the sales_details table with a derived table for the desired result. Observe the below query, it’s output, and explanation.

SELECT 
    sd1.*
FROM
    sale_details sd1
        INNER JOIN
    (SELECT 
        sale_person_name, MAX(no_products_sold) AS MaxSale
    FROM
        sale_details
    GROUP BY sale_person_name) sd2 ON sd1.sale_person_name = sd2.sale_person_name
        AND sd1.no_products_sold = sd2.MaxSale;

Action Output Message: SELECT sd1.* FROM sale_details sd1 INNER JOIN (SELECT sale_person_name, MAX(no_products_sold) AS MaxSale FROM sale_details GROUP BY sale_person_name) sd2 ON sd1.sale_person_name = sd2.sale_person_name AND sd1.no_products_sold = sd2.MaxSale LIMIT 0, 1000 4 row(s) returned 0.00097 sec / 0.000032 sec.

Output:-

figure 1.3

Explanation:- In this query, we are using an INNER JOIN with the sales_details table and the derived table.

STEP1:  The derived table created in the sub-query “SELECT sale_person_name, MAX(no_products_sold) AS MaxSale FROM sale_details GROUP BY sale_person_name” gets the sales_person_name and maximum no_products_sold grouped by sales_person_name. The alias name given to this derived table is sd2.

STEP2: Select all the details from the sales_details table in the outer-query alias name is sd1.

STEP3: Finally, doing an INNER JOIN on the derived table and sales_details table ON sale_person_name AND ON no_products_sold from sales_details table, MAX(no_products_sold) from the derived table.

We hope this article provided a good understanding of the alias while using the derived tables. Good Luck !!!

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top