I was working on this Query:

select ctable.code, company.founder, ctable.lman, ctable.sman, ctable.man, ctable.emp
from (select 
c.company_code as code, 
count(distinct l.lead_manager_code) as lman, 
count(distinct s.senior_manager_code) as sman,
count(distinct m.manager_code) as man,
count(distinct e.employee_code) as emp
from company c join lead_manager l on c.company_code = l.company_code
join senior_manager s on c.company_code = s.company_code
join manager m on c.company_code = m.company_code
join employee e on c.company_code = e.company_code 
group by c.company_code) AS ctable 
join company on company.company_code = ctable.code
order by ctable.code;

I was running into an error and I was scratching my head. I was getting a ‘Command not properly ended’ error.

I eventually figured out that if I removed the ‘AS’ at the end of the subquery the error goes away, like so:

select ctable.code, company.founder, ctable.lman, ctable.sman, ctable.man, ctable.emp
from (select 
c.company_code as code, 
count(distinct l.lead_manager_code) as lman, 
count(distinct s.senior_manager_code) as sman,
count(distinct m.manager_code) as man,
count(distinct e.employee_code) as emp
from company c join lead_manager l on c.company_code = l.company_code
join senior_manager s on c.company_code = s.company_code
join manager m on c.company_code = m.company_code
join employee e on c.company_code = e.company_code 
group by c.company_code) ctable 
join company on company.company_code = ctable.code
order by ctable.code;

As far as I am aware the AS operator is just a way to set an alias in a more verbose way, so why does it fail when trying to alias the subquery with AS vs. without AS? It is working when I alias the column names within the subquery with AS.