CODEWITHSUNDEEP
mysqlsql
Bratt Swan
Bratt Swan

Reputation: 1146

Alias table doesn't exist in where statement

The question is from https://code.dennyzhang.com/sales-analysis-i

My solution is:

select seller_id
from (select seller_id, sum(price) price
        from Sales
        group by seller_id) S
# where S.price = max(S.price)
where S.price = (select max(price) from S)

However, the console gives me the error message:

Table 'test.s' doesn't exist

I replaced the last line of code by

where S.price = 0

This doesn't give me any error messages meaning that S table does exist. So my question is how does this error message come from?

Upvotes: 0

Views: 397

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

S is a reference to a table that can be used to qualify columns. It cannot be used in a FROM clause.

You can do what you want using a CTE:

with S as (
      select seller_id, sum(price) as price
      from Sales
      group by seller_id
     ) 
select seller_id
from S
where S.price = (select max(price) from S)

Upvotes: 3

Related Questions