Query to include Count of NULL values in GROUP BY
-
Hi,
I have tables of following structure
Employee Table
EmpId DeptId GradeId
100 1 A1
101 1 A2
103 2 A1
104 3 A3
105 3 A3
Department Table
DeptId DeptName
1 Insurance
2 HealthCare
3 BPO
Grade Table
GradeId GradeName
A1 APM
A2 Manager
A3 TL
I want the result set like number of resource counts for each departments with each grade id. like below....
DeptId DeptName GradeId GradeName Count
1 Insurance A1 APM 1
1 Insurance A2 Manager 1
1 Insurance A3 TL 0
2 HealthCare A1 APM 1
2 HealthCare A2 Manager 0
2 HealthCare A3 TL 0
3 BPO A1 APM 0
3 BPO A2 Manager 0
3 BPO A3 TL 2
I tried query with usual GROUP BY.. but it doesnt return row if the count is null.
Please Help...
-
Do a cross join between departments and grades, then a correlated subquery on employees from that.
Would look something like:
select DeptID, DeptName, GradeID, GradeName,(select count(*)
from Employee
where DeptID = Department.DeptID
and GradeID = Grade.GradeID) as [Count]
from Department
cross join Grade;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (11/30/2009)
Do a cross join between departments and grades, then a correlated subquery on employees from that.Would look something like:
select DeptID, DeptName, GradeID, GradeName,(select count(*)
from Employee
where DeptID = Department.DeptID
and GradeID = Grade.GradeID) as [Count]
from Department
cross join Grade;
Similar but without subquery
SELECT d.DeptId,d.DeptName,g.GradeId,g.GradeName,
COUNT(e.EmpId) AS [Count]
FROM Department d
CROSS JOIN Grade g
LEFT OUTER JOIN Employee e ON e.DeptId=d.DeptId AND e.GradeId=g.GradeId
GROUP BY d.DeptId,d.DeptName,g.GradeId,g.GradeName
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark, that will produce a count of 1 if there are zero rows in the outer join, if I'm not mistaken. Test it and see what you get.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (12/1/2009)
Mark, that will produce a count of 1 if there are zero rows in the outer join, if I'm not mistaken. Test it and see what you get.The query uses COUNT(e.EmpId) not COUNT(*) so only non-NULL EmpIds will be considered
CREATE TABLE [dbo].[Grade]([GradeId] [varchar](10) NULL,
[GradeName] [varchar](10) NULL
)
GO
CREATE TABLE [dbo].[Employee](
[EmpId] [int] NULL,
[DeptId] [int] NULL,
[GradeId] [varchar](10) NULL
)
GO
CREATE TABLE [dbo].[Department](
[DeptId] [int] NULL,
[DeptName] [varchar](30) NULL
)
GO
INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (N'A1', N'APM')
INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (N'A2', N'Manager')
INSERT [dbo].[Grade] ([GradeId], [GradeName]) VALUES (N'A3', N'TL')
INSERT [dbo].[Employee] ([EmpId], [DeptId], [GradeId]) VALUES (100, 1, N'A1')
INSERT [dbo].[Employee] ([EmpId], [DeptId], [GradeId]) VALUES (101, 1, N'A2')
INSERT [dbo].[Employee] ([EmpId], [DeptId], [GradeId]) VALUES (103, 2, N'A1')
INSERT [dbo].[Employee] ([EmpId], [DeptId], [GradeId]) VALUES (104, 3, N'A3')
INSERT [dbo].[Employee] ([EmpId], [DeptId], [GradeId]) VALUES (105, 3, N'A3')
INSERT [dbo].[Department] ([DeptId], [DeptName]) VALUES (1, N'Insurance')
INSERT [dbo].[Department] ([DeptId], [DeptName]) VALUES (2, N'HealthCare')
INSERT [dbo].[Department] ([DeptId], [DeptName]) VALUES (3, N'BPO')
SELECT d.DeptId,d.DeptName,
g.GradeId,g.GradeName,
COUNT(e.EmpId) AS [Count]
FROM Department d
CROSS JOIN Grade g
LEFT OUTER JOIN Employee e ON e.DeptId=d.DeptId AND e.GradeId=g.GradeId
GROUP BY d.DeptId,d.DeptName,g.GradeId,g.GradeName
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
I sometimes use this to count NULLs
sum(case when [Field] is null then 1 else 0 end)
don't know how performant it is though
can be useful like this also
having sum(case when [banana] is null then 1 else 0 end) = 0
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply