Hi clever people
I have the following tables below called employee and department. What would be the best way to write a procedure where the procedure would take in a department name (sales for example) and return as count the number of males/females?
e.g. name male female
sales 10 5
what about returning the count for each individual department names if no department name was entered?
your help is very much appreciated.
Kind regards and thanks
employeeNo lastName firstName gender departmentID
1 Tom Smith M 2
2 Ted Adams M 2
3 Mary Thomas F 3
Department
departmentID name
2 Sales
3 Finance
4 Human Resources
5 IT
6 Production
7 Equipment
June 25, 2020 at 12:37 pm
Join the tables, group the result, pivot the result
create table #employee (
employeeNo nvarchar(20),
lastName nvarchar(20),
firstName nvarchar(20),
gender nvarchar(20),
departmentID nvarchar(20))
create table #department (
departmentID nvarchar(20),
"name" nvarchar(20))
insert into #employee(employeeNo,firstName,lastName,gender,departmentID)
values('1','Tom','Smith','M','2')
,('2','Ted','Adams','M','2')
,('3','Mary','Thomas','F','3')
insert into #department(departmentID,"name")
Values ('2','Sales')
,('3','Finance')
,('4','Human Resources')
,('5','IT')
,('6','Production')
,('7','Equipment')
--Get Name and Number of Genders
select a."name",b.gender,"Counter"=count(*)
into #PivotMe
from #department a
inner join #employee b on a.departmentID = b.departmentID
group by a."name",b.gender
--Pivot the result
select "NAME"
,F,M
from #PivotMe
PIVOT (SUM("Counter") for Gender in ([F],[M])) as pvt
I want to be the very best
Like no one ever was
select
a.[name],
sum(iif(b.gender='M', 1, 0)) m_count,
sum(iif(b.gender='F', 1, 0)) f_count
from
#department a
join
#employee b on a.departmentID = b.departmentID
group by
a.[name];
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply