Dynamic SQL query for count data

Question

I'm working on an application that counts the number of employees in each department.

Here's my code:

SELECT 
    count(case when (JS_TITLE = 'Accounting')then 1 end)as Accounting,
    count(case when (JS_TITLE = 'Management')then 1 end)as Management,
    count(case when(JS_TITLE = 'Marketing')then 1 end )as Marketing,
    count(case when(JS_TITLE = 'HR')then 1 end)as HR
FROM EMP
LEFT JOIN JOB_STATUS ON EMP.JS_REF = JOB_STATUS.JS_REF

but the thing is, the user may add a new department.

Is there a better way to make this static code more dynamic?

Answer 1

You can use a GROUP BY clause to group by JS_TITLE, then you must transpose the record into a column matrix:

SELECT 
    JS_TITLE, 
    count(EMP.JS_REF)
FROM EMP
LEFT JOIN JOB_STATUS ON EMP.JS_REF = JOB_STATUS.JS_REF
GROUP BY JS_TITLE