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.
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
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.