question

fashraf avatar image
fashraf asked

Group Join between three tables to get the percentage

I have three tables Attendance, Employee, Sector Employee Table EmiId -Name -SectorId 123 ABC 1 231 BCD 2 125 WER 1 Attendance AttId -EmpId -Dt 1 123 12/12/2014 9:00 2 231 12/12/2014 10:00 Sector SectorId -SectorName 1 North Sector 2 East Sector my query is SELECT COUNT(Attendance.Emp_Id) as AttCount,(select COUNT(*) from Employee) as EmpCount FROM Employee INNER JOIN Sector ON Employee.SectorId = Sector.SectorId INNER JOIN Attendance ON Employee.EmpId = Attendance.EmpId group by Sector.SectorId and i keep getting same number of employees for both instead so the (select COUNT(*) from Employee)- EmpCount seems to be incorrect.I keep getting the same number for both the sectors. Although the Attcount seems to work fine. Please help. Thanks in advance.
group-bymssqlinner join
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Not entirely sure what output you want (it would help if you could show us), but here's a query that gives the Attendance count and number of employees per sector.... declare @Employee table (EmpId int, name varchar(50), SectorId int) declare @Attendance table (AttId int, EmpId int, dt datetime) declare @Sector table (SectorId int, SectorName varchar(50)) insert into @Employee select 123,'ABC',1 insert into @Employee select 231,'BCD',2 insert into @Employee select 125,'WER',1 insert into @Attendance select 1,123, '12/12/14 09:00' insert into @Attendance select 1,231, '12/12/14 10:00' insert into @Sector select 1, 'North Sector' insert into @Sector select 2, 'South Sector' SELECT distinct Sector.SectorId, count(Attendance.EmpId)over (partition by Sector.SectorId) as AttCount, count(*)over (partition by Sector.SectorId) as EmpCount FROM @Employee Employee left join @Sector Sector on Employee.SectorId = Sector.SectorId left join @Attendance Attendance on Employee.EmpId = Attendance.EmpId
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
What average are you looking for? The average number of employees per sector? Or are you just looking for a count of employees per sector? With the "SELECT COUNT(*)" query you will always get the same number for all sectors. It's just returning the total number of rows from the Employee table so I would expect that to always return the same value for the number of "sector" groups that you have.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

fashraf avatar image fashraf commented ·
i am looking for the total attendance for the sector and the employees belonging to each sector (count). It keeps showing same number of employees for both sectors.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.