SQL Count from two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Count from two tables

Author

Topic

KJN94
Starting Member

2 Posts

Posted - 2015-01-04 : 11:19:39

Hello, can someone help me with this:

I have two tables. I have three columns DEPARTMENT_NAME, LAST_NAME, JOB_ID.
DEPARTMENT_NAME is from table 1. JOB_ID and LAST_NAME are from table 2. I would like
to count the number of employees for each DEPARTMENT_NAME and I want to be displayed DEPARTMENT_NAME, LAST_NAME, JOB_ID. But all I can do is to find the number of employees for each job_id. One DEPARTMENT_NAME has more than one JOB_ID.

Here is my code:
SELECT E.JOB_ID, D.DEPARTMENT_NAME, COUNT(E.LAST_NAME)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
GROUP BY E.JOB_ID, D.DEPARTMENT_NAME;

Please help!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-04 : 13:12:46

try group by rollup to get results by job and also by department

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2015-01-05 : 02:58:24

select JOB_ID,DEPARTMENT_NAME,count(LAST_NAME) as ct_last_name from( SELECT E.JOB_ID as JOB_ID , D.DEPARTMENT_NAME as DEPARTMENT_NAME ,E.LAST_NAME as LAST_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)f
GROUP BY JOB_ID;

http://sql-az.tr.gg/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources