Hi everyone! I’m new to working with databases and need some help with an Oracle SQL query.
I’m trying to write a query that will show me all the office locations where our company has staff members working, along with a count of how many people work at each location. I want the results sorted so that the location with the highest number of employees appears first.
Basically I need to:
Get a list of all locations that have employees
Count how many employees are at each location
Sort the results from highest to lowest employee count
I’ve been trying different approaches but can’t seem to get it right. Any suggestions on how to structure this query would be really appreciated!
Depends on your table setup, but if you’ve got separate employee and location tables, you’ll need a JOIN. Assuming your employees table has a location_id that links to your locations table, try this: SELECT l.location_name, COUNT(e.employee_id) FROM locations l INNER JOIN employees e ON l.location_id = e.location_id GROUP BY l.location_name ORDER BY COUNT(e.employee_id) DESC;. Main thing here is using INNER JOIN to connect the tables and counting employee IDs instead of COUNT(*). Just swap out the column names for whatever you’re actually using. This’ll handle the relational stuff and get you what you need.
sounds like you need a basic GROUP BY query! try something like select location, count(*) from employees group by location order by count(*) desc; - assuming ur employee table has a location column. might need to adjust column names tho
hey iris72! just wondering, do the employees and locations exist in different tables? also, are there locations with no employees? this might affect your joins and query structure.