Query to include Count of NULL values in GROUP BY

Question

Post reply

Query to include Count of NULL values in GROUP BY

itzmee

Say Hey Kid

Points: 700
More actions
November 30, 2009 at 9:43 am

#96124

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 2

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

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 4

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