CODEWITHSUNDEEP
sqloracle-databasejoincount
Sobasofly
Sobasofly

Reputation: 379

SQL count different jobs in each departmen

I cant figure out a way to show the amount of different jobs that are in each department. The output I get counts all the job_ids even the same ones but I only want it to show the number of different jobs for that city's department. 

SELECT DISTINCT DEPARTMENT_ID, substr(DEPARTMENT_NAME, 0, 15), 
substr(NVL(CITY, 'Not Assigned Yet'), 0, 20) "City", 
COUNT(JOB_ID) "# of Jobs"
from EMPLOYEES
LEFT OUTER JOIN DEPARTMENTS
USING(DEPARTMENT_ID)
LEFT OUTER JOIN LOCATIONS
USING(LOCATION_ID)
GROUP BY DEPARTMENT_ID, DEPARTMENT_NAME, CITY

All help will be appreciated thanks.

Upvotes: 2

Views: 463

Answers (1)

Bohemian
Bohemian

Reputation: 425198

Use COUNT(DISTINCT JOB_ID), which counts how many different values there are:

SELECT DEPARTMENT_ID, substr(DEPARTMENT_NAME, 0, 15), 
substr(NVL(CITY, 'Not Assigned Yet'), 0, 20) "City", 
COUNT(DISTINCT JOB_ID) "# of Jobs"
FROM EMPLOYEES
LEFT OUTER JOIN DEPARTMENTS
USING(DEPARTMENT_ID)
LEFT OUTER JOIN LOCATIONS
USING(LOCATION_ID)
GROUP BY DEPARTMENT_ID, DEPARTMENT_NAME, CITY

And you don't need SELECT DISTINCT - just SELECT.

Upvotes: 3

Related Questions