I want to find sum total of the count of hospitals, I have written following query, Its showing error: Every derived table must have its own alias

Question

#Total No of hospitals from each state# select sum(num_of_hosp) from (select count(distinct hospital) as num_of_hosp from project.dataset where score != 'not available' group by state order by num_of_hosp DESC)

I have tried above mention query and could not find the required answer. I want to find sum of the count of hospitals from each state

Answer 1

As indicated by the error, you need an alias for your derived table. The sub-query in parenthesis after your first from creates what is known as a derived table and you must provide an alias:

select sum(num_of_hosp) 
from (
  select count(distinct hospital) as num_of_hosp 
  from project.dataset 
  where score != 'not available' 
  group by state
)MyAlias;

Note, ordering the rows in your derived table is redundant here too.