give me the sql statement

[murugapandiyan]

hi how to i get the details following from employee table

total no of employee in each department and also total no male and female.
that is department name: total no of male: total no Female: Total Employee

[Jim Doherty]

hi how to i get the details following from employee table

total no of employee in each department and also total no male and female.
that is department name: total no of male: total no Female: Total Employee

Assuming you have a table called Employee with fields Department,Gender, EmpNo

Paste this into query analyser run it and it will create the view (I say this because the graphical view window doesnt support the case statement but the SQL is valid and the view will be created

The output will be a matrix subtotalling each gender and having subtotals for each department and a grandtotal at the bottom of the stack

 
 
CREATE VIEW vw_Employees_Dept AS
select case when (grouping(department) = 1)
then 'grand total' else isnull(department, 'unknown') end as Department,
case when (grouping(gender) = 1) 
then 'Subtotal '+department else isnull(gender, 'unknown') end as Gender,
count(empno) as Total
from Employee
group by department, gender with rollup
GO
 
 

Regards

Jim :)

[amitpatel66]

hi how to i get the details following from employee table

total no of employee in each department and also total no male and female.
that is department name: total no of male: total no Female: Total Employee

Could you please post your Employee table structure here for reference?