In this Tutorial, We will learn How to Count Male and Female Without Case Statement in SQL Server. Counting male and female in SQL Without using a Case statement by using the SUM function with a conditional expression.
First we need to create Table
CREATE TABLE [dbo].[EmployeeV]( ID int NOT NULL, [FirstName] Varchar(200) NOT NULL, [MiddleName] Varchar(200) NOT NULL, [LastName] Varchar(200) NOT NULL, [JobTitle] Varchar(200) NOT NULL, [Department] Varchar(200) NOT NULL, [Gender] [nchar](1) NULL )
Then Insert data into EmployeeV Table
INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (1, N'Ken', N'J', N'Sánchez', N'Chief Executive Officer', N'Executive', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (2, N'Terri', N'Lee', N'Duffy', N'Vice President of Engineering', N'Engineering', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (3, N'Roberto', NULL, N'Tamburello', N'Engineering Manager', N'Engineering', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (4, N'Rob', NULL, N'Walters', N'Senior Tool Designer', N'Tool Design', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (5, N'Gail', N'A', N'Erickson', N'Design Engineer', N'Engineering', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (6, N'Jossef', N'H', N'Goldberg', N'Design Engineer', N'Engineering', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (7, N'Dylan', N'A', N'Miller', N'Research and Development Manager', N'Research and Development', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (8, N'Diane', N'L', N'Margheim', N'Research and Development Engineer', N'Research and Development', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (9, N'Gigi', N'N', N'Matthew', N'Research and Development Engineer', N'Research and Development', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (10, N'Michael', NULL, N'Raheem', N'Research and Development Manager', N'Research and Development', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (11, N'Ovidiu', N'V', N'Cracium', N'Senior Tool Designer', N'Tool Design', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (12, N'Thierry', N'B', N'D''Hers', N'Tool Designer', N'Tool Design', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (13, N'Janice', N'M', N'Galvin', N'Tool Designer', N'Tool Design', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (14, N'Michael', N'I', N'Sullivan', N'Senior Design Engineer', N'Engineering', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (15, N'Sharon', N'B', N'Salavaria', N'Design Engineer', N'Engineering', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (16, N'David', N'M', N'Bradley', N'Marketing Manager', N'Marketing', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (17, N'Kevin', N'F', N'Brown', N'Marketing Assistant', N'Marketing', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (18, N'John', N'L', N'Wood', N'Marketing Specialist', N'Marketing', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (19, N'Mary', N'A', N'Dempsey', N'Marketing Assistant', N'Marketing', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (20, N'Wanida', N'M', N'Benshoof', N'Marketing Assistant', N'Marketing', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (21, N'Terry', N'J', N'Eminhizer', N'Marketing Specialist', N'Marketing', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (22, N'Sariya', N'E', N'Harnpadoungsataya', N'Marketing Specialist', N'Marketing', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (23, N'Mary', N'E', N'Gibson', N'Marketing Specialist', N'Marketing', N'F')
Now Execute the Count the Male and Female query. This query will return the number of male and female separately in two different columns. Here’s an example
SELECT
SUM(IIF(GENDER = 'M', 1, 0)) AS Male,
SUM(IIF(GENDER = 'F', 1, 0)) AS Female
FROM
EmployeeV
Now Execute the Count the Male and Female query. This query will return the number of male and female in each department separately in two different columns. Here’s an example
SELECT
Department,
SUM(IIF(GENDER = 'M', 1, 0)) AS Male,
SUM(IIF(GENDER = 'F', 1, 0)) AS Female
FROM
EmployeeV
group by Department
Step by Step Explanation of the SQL Query
- SELECT – SELECT statement to retrieve the male and female count from table.
- SUM Function – Count the Males and Females. The SUM function takes an expression that evaluates to 1 if the condition is True and 0 if the condition is False.
- IIF Function – return a value if a condition is TRUE or another value if a condition is False.
