1
\$\begingroup\$

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?

\$\endgroup\$
1
\$\begingroup\$

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
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.