CODEWITHSUNDEEP
oracle-sqldeveloper
Tim
Tim

Reputation: 101

Using Count Function and Percentage of Count Total in One Select Statement

I have three data tables Employees, Departments, and Locations. I want to show the total number of employees in each state and what percentage of the employees are located in that each state. The Employees table and the Departments table have one identical column called Department_ID, and the Departments table and the Locations table have one identical column called Location_ID. Here's what I wrote for my code:

select l.state_province e.count(*) as "Employees in State",
e.count(*)*100/sum(e.count(*)) over ()
from employees e
full outer join departments d on e.department_id = d.department_id
full outer join locations l on l.location_id = d.location_id
order by l.state_province;

However, the error "from keyword not found where expected" shows up when I run the code. How do I fix it?

Upvotes: 0

Views: 16

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You need group by. And regular joins should be fine:

select l.state_province, count(*) as "Employees in State",
       count(*) * 100/sum(count(*)) over ()
from employees e join
     departments d
     on e.department_id = d.department_id join
     locations l
     on l.location_id = d.location_id
group by l.state_province
order by l.state_province;

Upvotes: 1

Related Questions