BLACK FRIDAY EXTRA SAVINGS EVENT - EXTENDED
START FREE

Lack of alias returns an error

https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/4/analyzing-employee-sales-performance

My Code:

%%sql
WITH total_sales_report AS
    (
     SELECT SUM(i.total) ,
            c.support_rep_id
     FROM invoice i
     INNER JOIN customer c ON c.customer_id = i.customer_id
     GROUP BY 2
    )
SELECT e.first_name ||' '|| e.last_name employee_name,
       e.title,
       SUM(tsr.total) total_sales
FROM total_sales_report tsr
INNER JOIN employee e ON e.employee_id = tsr.support_rep_id
GROUP BY 1;

What I expected to happen:

employee_name title total_sales
Jane Peacock Sales Support Agent 1731.510000000004
Margaret Park Sales Support Agent 1584.0000000000032
Steve Johnson Sales Support Agent 1393.9200000000028

What actually happened:

operational error message

The fourth line of SELECT SUM(i.total) , only works if it is SELECT SUM(i.total) total,.

I’m confused as to why it is requiring the specific alias of ‘total’ in order to return the expected result.

1 Like

tsr which is the CTE you wrote must output a column named total in its outermost select. Without alias, this column exists as the string SUM(i.total) and later queries using this CTE will need to write the whole expression to select the column if there’s no column alias.

What is the full error message exactly? Is it OperationalError: no such column.
I doubt the error message provided 0 hints to what’s the issue.

3 Likes

Thanks, that makes total sense!

The rest of the error was no such column: tsr.total, which I didn’t understand.

Thanks again!