SQL ERROR "Every derived table must have its own alias"

Question

This is my query in SQL.

SELECT MAX(course_id), sec_id, COUNT(ID) 
FROM (SELECT MAX(course_id), sec_id, COUNT(ID) 
      FROM section 
      NATURAL JOIN takes 
      GROUP BY course_id);

When executed, I'm getting this error:

<"Every derived table must have its own alias">

your subquery needs to alias the aggregated columns in order for you to use those names in the outer query. The `select max(course_ID), sec_ID, count(ID)` will error because course_ID and ID don't exist in your subquery. To prove it run the subquery on it's own and look at the column names it gives for cols 1 and 3. — xQbert, Oct 24 '17 at 18:41

bassxzero · Answer 1 · 2017-10-24T17:42:18.590

1

Add an alias to your derived table.

SELECT 
    MAX(course_id), 
    sec_id, 
    COUNT(ID) 
FROM 
    (
        SELECT 
            MAX(course_id), 
            sec_id, 
            COUNT(ID) 
        FROM 
            section NATURAL 
            JOIN takes 
        GROUP BY 
            course_id
    ) `A`;

edited Oct 24 '17 at 17:42

answered Oct 24 '17 at 17:34

bassxzero

4,838
22
34

it gives me this error " Unknown column 'A.course_id' in 'field list' – Akmaljon Usmonov Oct 24 '17 at 17:41
@AkmaljonUsmonov Sorry. Try the updated query. – bassxzero Oct 24 '17 at 17:42
bassxzero, it still gives me this error. ((( – Akmaljon Usmonov Oct 24 '17 at 17:45
@AkmaljonUsmonov because you need to alias `max(course_ID) as course_ID` and `count(ID) as ID` – xQbert Oct 24 '17 at 18:39

SQL ERROR "Every derived table must have its own alias"

1 Answers1