[vibinvictoria]

Hi Experts,

I have two tables. Employee and dept table

Employee

id - name - dept_id -[fk ref. dept (dept_id)]
1 ---aaa---1
2 ---bbb---1
3 --ccc ---2
4 ---ddd --2

in dept table
dept_id- depart_name
1--Mech
2-- Ele
3 --Civil

Want to print depar_id,dept_name and count of number of employee's associated with corresponding dept_id.
like this below

dept_id--dept_name--total
1-------mech -------2
2------Ele----------2
3------civil--------0

I tried , but no luck.

[Atli]

Hey.

This can be done using a GROUP BY clause and the COUNT function. The idea is to have the SQL query group each department into a single line, and then count how many items are being compacted into that one line.

The first step is to create a query to list all the departments and all the employees associated with each department. So, something like this:

SELECT d.id, d.name, e.id
FROM departments AS d
JOIN employees AS e
    ON d.id = e.department_id

That'll give you a complete list of employees and their assigned departments.

The next step is to reduce this set so that each department only gets one row, regardless of how many employees it has. That can be achieved adding the GROUP BY clause.

SELECT d.id, d.name, e.id
FROM departments AS d
JOIN employees AS e
    ON d.id = e.department_id
GROUP BY d.id

Now what you have is a list of departments, each with the ID of the first employee associated with it. Of course, this isn't horribly useful by itself.

Which brings us to step three: the COUNT function. The thing about that function, when used with the GROUP BY clause, it'll count all the rows that would have been listed for that group, even though they've been reduced to one row. So by adding a column to the field set that uses the COUNT function, we get the number of employees associated with each group.

SELECT d.id, d.name, COUNT(e.id) AS employee_count
FROM departments AS d
JOIN employees AS e
    ON d.id = e.department_id
GROUP BY d.id

[andrewsw]

Quote

I tried , but no luck.

Show us the statement that you have tried.

[vibinvictoria]

Hi alti,

Thanks for ur quick response

Tried the same query

SELECT 

INFO.DEPT_ID,
COUNT(*) FROM employee INFO
JOIN  MY_DEPT DEPT ON INFO.DEPT_ID=DEPT.DEPT_ID
 GROUP BY INFO.DEPT_ID;

but it given employee count who are all associated with department.
But also i need to display with department with 0 count [ie. no employee]. In the example which i shared, department civil , doesn't have any employee. we need to display civil also. please advice.

This post has been edited by vibinvictoria: 31 January 2013 - 11:35 AM

[Atli]

OK. The standard "JOIN" will exclude rows from the parent table when the ON clause cant be satisfied. So if there are no employees associated with a department, that department will be excluded. That can be fixed by using a "LEFT OUTER JOIN" instead. Then the department row will still be included, just with a NULL for the employee ID.

So, use "LEFT OUTER JOIN" (or just "LEFT JOIN") instead of the "JOIN". That should fix that issue.

One other thing, though, that I notice about your query. Your goal is to list all the departments, with the number of employees associated with each department listed. Right? If so, then your tables are reversed. You want to be using the "department" table in the FROM clause and then joining the "employees" table, not the other way around.

[vibinvictoria]

Hi Atli,

used left outer join

SELECT DEPT.DEPT_ID,COUNT(*)
FROM MY_DEPT DEPT 
LEFT OUTER JOIN employee INFO ON INFO.DEPT_ID=DEPT.DEPT_ID GROUP BY DEPT.DEPT_ID;

the above query given , non -associated department id [ie. dept_id is 3] count "1" instead of 0.
Please advice.



Thanks in advance,

This post has been edited by vibinvictoria: 31 January 2013 - 12:00 PM

[Atli]

The COUNT function, if used with the wild-card char (*) will pretty much just count the number of rows. However, if you use a specific column, it should only count non-NULL fields. So in order to get the correct employee number when there are no employees associated with the department, use one of the employee fields in the COUNT function, like I do in the example I posted earlier.

[vibinvictoria]

Hi Atli,

Thanks a lot man.. Got it as expected.
Once again i thank u atli

Got it..

SELECT DEPT.DEPT_ID,COUNT(INFO.DEPT_ID)
FROM MY_DEPT DEPT 
LEFT  JOIN employee INFO ON INFO.DEPT_ID=DEPT.DEPT_ID GROUP BY DEPT.DEPT_ID;

Thanks in advance,