SQL Count from two tables

Question

SQL Count from two tables

knikolov4521

SSC Veteran

Points: 272
More actions
January 4, 2015 at 9:31 am

#293441

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!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

knikolov4521 (1/4/2015)
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!

1) I see nothing that links the two tables together.

2) Please provide create table statements and some sample data and expected output.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

knikolov4521 SSC Veteran Points: 272 More actions · Answer 2

The tables are linked by these lines:

FROM EMPLOYEES E JOIN DEPARTMENTS D

ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)

The names of the tables are called: EMPLOYEES and DEPARTMENTS

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

knikolov4521 (1/4/2015)
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!

Quick suggestion, use the OVER clause in the aggregation to retain the details

😎

SELECT

E.JOB_ID

,D.DEPARTMENT_NAME

,COUNT(E.LAST_NAME) OVER

(

PARTITION BY D.DEPARTMENT_NAME

) AS DEP_LAST_NAME_COUNT

FROM EMPLOYEES E

INNER JOIN DEPARTMENTS D

ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);

knikolov4521 SSC Veteran Points: 272 More actions · Answer 4

Thank you! But I do not want to for example IT_PROG to be displayed 5 times. Please take a look at the attached picture. I tried to use DISTINCT, but it gave me an error.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

knikolov4521 (1/4/2015)
Thank you! But I do not want to for example IT_PROG to be displayed 5 times. Please take a look at the attached picture. I tried to use DISTINCT, but it gave me an error.

Can't see the picture.

Also, what is the error you are getting. Doesn't do us any good to just say it gave you an error.

knikolov4521 SSC Veteran Points: 272 More actions · Answer 6

Here is the picture: https://www.dropbox.com/s/v9l4sp6o2lml101/2.PNG?dl=0

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

knikolov4521 (1/4/2015)

Here is the picture: https://www.dropbox.com/s/v9l4sp6o2lml101/2.PNG?dl=0%5B/quote%5D

Also need the error you get when using the DISTINCT. Better yet, post your query using it as well.

knikolov4521 SSC Veteran Points: 272 More actions · Answer 8

I made it. Thank you. If I have any other questions I will post them. Thanks!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

knikolov4521 (1/4/2015)
I made it. Thank you. If I have any other questions I will post them. Thanks!

Well, if you fixed it, show it. It may help others with a similar issue.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 10

knikolov4521 (1/4/2015)
Thank you! But I do not want to for example IT_PROG to be displayed 5 times.

Quick fix using row_number in a sub query, just adjust as needed

😎

USE tempdb;

GO

SET NOCOUNT ON;

SELECT

X.JOB_ID

,X.DEPARTMENT_NAME

,X.DEP_LAST_NAME_COUNT

FROM

(

SELECT

E.JOB_ID

,D.DEPARTMENT_NAME

,COUNT(E.LAST_NAME) OVER

(

PARTITION BY D.DEPARTMENT_NAME

) AS DEP_LAST_NAME_COUNT

,ROW_NUMBER() OVER

(

PARTITION BY D.DEPARTMENT_NAME

,E.JOB_ID

) AS DEP_RID

FROM EMPLOYEES E

INNER JOIN DEPARTMENTS D

ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)

) AS X

WHERE X.DEP_RID = 1;

knikolov4521 SSC Veteran Points: 272 More actions · Answer 11

SELECT

DISTINCT E.JOB_ID

,D.DEPARTMENT_NAME

,COUNT(E.LAST_NAME) OVER

(

PARTITION BY D.DEPARTMENT_NAME

) AS DEP_LAST_NAME_COUNT

FROM EMPLOYEES E

INNER JOIN DEPARTMENTS D

ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);

knikolov4521 SSC Veteran Points: 272 More actions · Answer 12

How can I count the rows when I make new table. Here is my code:

SELECT EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID, COUNT(TO_CHAR(HIRE_DATE, 'DAY')) AS NUM_EMPLOYEES

FROM EMPLOYEES

WHERE TO_CHAR(HIRE_DATE, 'Day') = 'Monday'

GROUP BY EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID;

Here is the table: https://www.dropbox.com/s/03m2o6fzn1j8f8t/efewfewfewfew.PNG?dl=0

I have 10 rows, so I would like under NUM_EMPLOYEES to be displayed 10 on each row.

Please help!