CODEWITHSUNDEEP
sqlsql-serversql-server-2008
armarzook
armarzook

Reputation: 11

How to use count statement to get information from multiple tables in sql server?

How can I make a query for getting the number of employees on each department using a count function with the fields Dept_id, Dept_name, Number of employees using the data structure below?

create database Emp_det

create table Dept
(
    Dept_ID int not null primary key,
    Dept_name varchar(255)
)

create table Emp_table
(
    Emp_no int not null primary key,
    Emp_name varchar(255) not null,
    Dept_ID int foreign key references Dept(Dept_ID),
    Emp_status varchar(255)
)

create table Salary_table
(
    Emp_no int not null foreign key references Emp_table(Emp_no),
    From_date datetime,
    End_date datetime,
    Basic int,
    HRA int,
    Others int,
    Total int,
    Emp_status varchar(255),

    constraint pk_emp_det primary key (emp_no,From_date)
);

insert into Dept
values(10, 'I.T'), (11, 'H.R'),(12, 'Procurement'),(13, 'QS');

insert into Emp_table
values(1111,'Manivannan','10','A'),
(1222,'Faizal','10','A'),
(4122,'Marzook','10','A'),
(1223,'Venu','11','A');

insert into Salary_table
values(1111,'01/09/2012','1/10/2012',10000,10000,2000,22000,'A'),
(1222,'01/09/2012','1/10/2012',5000,5000,1000,11000,'A'),
(4122,'01/09/2012','1/10/2012',1000,1000,5000,2500,'A'),
(1223,'01/09/2012','1/10/2012',10000,10000,2000,22000,'A')

Upvotes: 1

Views: 409

Answers (2)

Lamak
Lamak

Reputation: 70638

SELECT D.Dept_ID, D.Dept_Name, COUNT(*) NumberOfEmployees
FROM Dept D
LEFT JOIN dbo.Emp_Table ET
ON D.Dept_ID = ET.Dept_ID 
GROUP BY D.Dept_ID, D.Dept_Name

This will list all departments, even if there are no employees. You can change the LEFT JOIN with an INNER JOIN if you only want departments that have employees.

Upvotes: 4

Beth
Beth

Reputation: 9607

    select 
      Dept_id, Dept_name,count(distinct Emp_no) as Number_of_employees
    from 
      Dept d inner join
      Emp_table e on 
      d.Dept_ID = e.Dept_ID
    group by
      Dept_id, Dept_name

sorry, did it off the top of my head. forgot the group by.

Upvotes: 1

Related Questions