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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

yo_beeDirector of Information TechnologyCommented:
Why the extra select for the query?
0
PortletPaulEE Topic AdvisorCommented:
>>"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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.