How to Count Male ,Female and Total in Single Query using SQL Server

- by Ahmed S

In this article, I will show you how to Count Male, Female  and Total in Single Query using SQL Server. 

Here I am using CASE Statement with COUNT Function.    

Let’s create a table tblEmployees  


CREATE TABLE tblEmployees(
    EmployeeID int IDENTITY(1,1) NOT NULL,
    FirstName varchar(10) NOT NULL,
    LastName varchar(20) NOT NULL,  
    Gender varchar(10) NULL,
    Title varchar(30) NULL, 
    BirthDate datetime NULL,
    HireDate datetime NULL,
    Address varchar(60) NULL,
    City varchar(15) NULL,
    Region varchar(15) NULL,
    Country varchar(15) NULL
)

 Then execute Insert Command to Insert Data into tblEmployees Table

 

INSERT [dbo].[tblEmployees] ([EmployeeID], [FirstName],[LastName], [Gender],[Title], [BirthDate],[HireDate], [Address],[City], [Region],[Country])

VALUES (1, N’Nancy’, N’Davolio’, N’F’, N’Sales Representative’, CAST(N’1948-12-08 00:00:00.000′AS DateTime), CAST(N’1992-05-01 00:00:00.000′AS DateTime), N’507 – 20th Ave. E.

Apt. 2A’, N’Seattle’, N’WA’, N’USA’)

INSERT [dbo].[tblEmployees] ([EmployeeID], [FirstName],[LastName], [Gender],[Title], [BirthDate],[HireDate], [Address],[City], [Region],[Country])

VALUES (2, N’Andrew’, N’Fuller’, N’M’, N’Vice President, Sales’, CAST(N’1952-02-19 00:00:00.000′AS DateTime), CAST(N’1992-08-14 00:00:00.000′AS DateTime), N’908 W. Capital Way’, N’Tacoma’, N’WA’, N’USA’)

INSERT [dbo].[tblEmployees] ([EmployeeID], [FirstName],[LastName], [Gender],[Title], [BirthDate],[HireDate], [Address],[City], [Region],[Country])

VALUES (3, N’Janet’, N’Leverling’, N’F’, N’Sales Representative’, CAST(N’1963-08-30 00:00:00.000′AS DateTime), CAST(N’1992-04-01 00:00:00.000′AS DateTime), N’722 Moss Bay Blvd.’, N’Kirkland’, N’WA’, N’USA’)

INSERT [dbo].[tblEmployees] ([EmployeeID], [FirstName],[LastName], [Gender],[Title], [BirthDate],[HireDate], [Address],[City], [Region],[Country])

VALUES (4, N’Margaret’, N’Peacock’, NULL, N’Sales Representative’,CAST(N’1937-09-19 00:00:00.000′ ASDateTime), CAST(N’1993-05-03 00:00:00.000′ ASDateTime), N’4110 Old Redmond Rd.’,N’Redmond’, N’WA’, N’USA’)

INSERT [dbo].[tblEmployees] ([EmployeeID], [FirstName],[LastName], [Gender],[Title], [BirthDate],[HireDate], [Address],[City], [Region],[Country])

 VALUES (5, N’Steven’, N’Buchanan’, N’M’, N’Sales Manager’, CAST(N’1955-03-04 00:00:00.000′ ASDateTime), CAST(N’1993-10-17 00:00:00.000′ ASDateTime), N’14 Garrett Hill’, N’London’, NULL, N’UK’)

INSERT [dbo].[tblEmployees] ([EmployeeID], [FirstName],[LastName], [Gender],[Title], [BirthDate],[HireDate], [Address],[City], [Region],[Country])

 VALUES (6, N’Michael’, N’Suyama’, N’M’, N’Sales Representative’,CAST(N’1963-07-02 00:00:00.000′ ASDateTime), CAST(N’1993-10-17 00:00:00.000′ ASDateTime), N’Coventry House

Miner Rd.’, N’London’, NULL, N’UK’)

INSERT [dbo].[tblEmployees] ([EmployeeID], [FirstName],[LastName], [Gender],[Title], [BirthDate],[HireDate], [Address],[City], [Region],[Country])

VALUES (7, N’Robert’, N’King’, NULL, N’Sales Representative’,CAST(N’1960-05-29 00:00:00.000′ ASDateTime), CAST(N’1994-01-02 00:00:00.000′ ASDateTime), N’Edgeham Hollow

Winchester Way’, N’London’, NULL, N’UK’)

INSERT [dbo].[tblEmployees] ([EmployeeID], [FirstName],[LastName], [Gender],[Title], [BirthDate],[HireDate], [Address],[City], [Region],[Country])

VALUES (8, N’Laura’, N’Callahan’, N’F’, N’Inside Sales Coordinator’, CAST(N’1958-01-09 00:00:00.000′AS DateTime), CAST(N’1994-03-05 00:00:00.000′AS DateTime), N’4726 – 11th Ave. N.E.’, N’Seattle’, N’WA’, N’USA’)

INSERT [dbo].[tblEmployees] ([EmployeeID], [FirstName],[LastName], [Gender],[Title], [BirthDate],[HireDate], [Address],[City], [Region],[Country])

VALUES (9, N’Anne’, N’Dodsworth’, N’F’, N’Sales Representative’, CAST(N’1966-01-27 00:00:00.000′AS DateTime), CAST(N’1994-11-15 00:00:00.000′AS DateTime), N’7 Houndstooth Rd.’, N’London’, NULL, N’UK’) 

This query return designation wise Male, Female and Not Assigned gender count with Total Employee.


SELECT Title,
COUNT(CASE WHEN Gender=‘M’ THEN 1  END) As Male,
COUNT(CASE WHEN Gender=‘F’ THEN 1  END) As Female,
COUNT(CASE WHEN (Gender=‘‘ OR Gender IS Null) THEN 1 END) As ‘NotAssigned’,
COUNT(*) as Total
FROM tblEmployees GROUP BY Title

This query return designation wise with specified city-wise count male and female.


SELECT Title,
COUNT(CASE WHEN Gender=‘M’ THEN 1  END) As Male,
COUNT(CASE WHEN Gender=‘F’ THEN 1  END) As Female,
COUNT(CASE WHEN (Gender=‘‘ OR Gender IS Null) THEN 1 END) As ‘NotAssigned’,
COUNT(*) as Total
FROM tblEmployees where city=‘London’ GROUP BY Title


Between Date range count male and female

SELECT Title,
COUNT(CASE WHEN Gender=‘M’ THEN 1  END) As Male,
COUNT(CASE WHEN Gender=‘F’ THEN 1  END) As Female,
COUNT(CASE WHEN (Gender IS Null) THEN 1 END) As ‘NotAssigned’,
COUNT(*) as Total
FROM tblEmployees  
where  CONVERT(date,birthDate,111)>=‘1948-12-08′ AND 
  CONVERT(date,birthDate,111)<=‘1963-08-30′ 
Group by Title 

 


Result


Related Posts

About Ahmed S

View all posts by Ahmed S →

One Comment on “How to Count Male ,Female and Total in Single Query using SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *