sql server count function

Hi experts,

I'm using sql server 2008.

I have a table that looks like this.

table
This is the script to create this table

USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestEmployees](
	[EmployeeID] [int] NOT NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](50) NULL,
	[DepartmentID] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (1, N'Smith', N'John', 1)
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (2, N'Johnson', N'Todd', 1)
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (3, N'Williams', N'Reggie', 1)
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (4, N'Jackson', N'Bruce', 2)
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (5, N'Butler', N'Hugh', 2)
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (6, N'Simpson', N'Angela', 3)
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (7, N'Lee', N'Maggie', 3)
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (8, N'Perkins', N'Billy', 4)
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (9, N'Steiner', N'Joe', 4)
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (10, N'Hardy', N'Lauren', 5)
GO

Open in new window


When I run this query

DECLARE @DepartmentID as int
SET @DepartmentID = 1
-----------------------
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[DepartmentID]
FROM [TestDatabase].[dbo].[TestEmployees]
WHERE [DepartmentID] = @DepartmentID

Open in new window


I get this:

RS1.PNG
When I run this query

DECLARE @DepartmentID as int
SET @DepartmentID = 2
-----------------------
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[DepartmentID]
FROM [TestDatabase].[dbo].[TestEmployees]
WHERE [DepartmentID] = @DepartmentID

Open in new window


I get this:

RS2.PNG

When I run this query

DECLARE @DepartmentID as int
SET @DepartmentID = 7
-----------------------
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[DepartmentID]
FROM [TestDatabase].[dbo].[TestEmployees]
WHERE [DepartmentID] = @DepartmentID

Open in new window


I get this:

RS3.PNG

How do revise my query to get the following result. I basically want the count of the records for each DepartmentID

when my input is this:
DECLARE @DepartmentID as int
SET @DepartmentID = 1

my result should look like this:
D1Count.PNG
when my input is this:
DECLARE @DepartmentID as int
SET @DepartmentID = 2

my result should look like this:
D2Count.PNG
when my input is this:
DECLARE @DepartmentID as int
SET @DepartmentID = 7

Notice how there are no records for DepartmentID = 7. So instead of returning null , I want to just show zero.
my result should look like this:
D7Count.PNGD1Count.PNG
D2Count.PNG
LVL 1
maqskywalkerAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
Demonstration:

    CREATE TABLE [TestEmployees](
    	[EmployeeID] [int] NOT NULL,
    	[LastName] [varchar](50) NULL,
    	[FirstName] [varchar](50) NULL,
    	[DepartmentID] [int] NULL
    )
    ;
    
    INSERT [TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) 
    VALUES 
     (1, N'Smith', N'John', 1)
    ,(2, N'Johnson', N'Todd', 1)
    ,(3, N'Williams', N'Reggie', 1)
    ,(4, N'Jackson', N'Bruce', 2)
    ,(5, N'Butler', N'Hugh', 2)
    ,(6, N'Simpson', N'Angela', 3)
    ,(7, N'Lee', N'Maggie', 3)
    ,(8, N'Perkins', N'Billy', 4)
    ,(9, N'Steiner', N'Joe', 4)
    ,(10, N'Hardy', N'Lauren', 5)
    ;

Open in new window

    DECLARE @DepartmentID AS int
    SET @DepartmentID = 7
    
    SELECT (
                SELECT
                      COUNT([EmployeeID])
                FROM [TestEmployees]
                WHERE [DepartmentID] = @DepartmentID
          )
          AS departmentcount

Open in new window

    | departmentcount |
    |-----------------|
    |               0 |

Open in new window

see: http://sqlfiddle.com/#!6/f8f99/2
0
 
PortletPaulfreelancerCommented:
DECLARE @DepartmentID AS int
SET @DepartmentID = 7

SELECT (
            SELECT
                  COUNT([EmployeeID])
            FROM [TestDatabase].[dbo].[TestEmployees]
            WHERE [DepartmentID] = @DepartmentID
      )
      AS departmentcount

Open in new window

0
 
yo_beeDirector of Information TechnologyCommented:
Are you looking to add this as a column or just the count?

For count only

SELECT count([DepartmentID])
FROM [TestDatabase].[dbo].[TestEmployees]
WHERE [DepartmentID] = @DepartmentID
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
yo_beeDirector of Information TechnologyCommented:
Why the extra select for the query?
0
 
PortletPaulfreelancerCommented:
>>"Notice how there are no records for DepartmentID = 7. So instead of returning null , I want to just show zero."

You are right, I don't need the extra select for a count.
0
 
Mark WillsTopic AdvisorCommented:
@maqskywalker
 
Very nicely laid out question.

You have included scripts and clearly enunciated your challenge. Thankyou.

I dont have a solution for you, that has already been supplied, but wanted to commend your approach :)

Cheers,
Mark Wills
5
 
Scott PletcherSenior DBACommented:
More broadly, if you want to see the count of employees in every department, and 0 if there are currently no employees in that dept, do something like this:

SELECT d.DepartmentID, COUNT(te.DepartmentID) AS Emp_Count
FROM dbo.Departments d
LEFT OUTER JOIN dbo.TestEmployees te ON te.DepartmentID = d.DepartmentID
0
 
Ganesh GuruduSenior ConsultantCommented:
try like this


DECLARE @DepartmentID AS int
SET @DepartmentID = 7

            SELECT
                  COUNT(isnull( [EmployeeID],0))  as departmentcount
            FROM [TestDatabase].[dbo].[TestEmployees]
            WHERE [DepartmentID] = @DepartmentID;
0
 
maqskywalkerAuthor Commented:
thanks
0
 
Ramesh D JaiswalCommented:
excellent solution Scott and Portlet
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.