SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.EMPLOYEE (
	EMPLOYEE_ID bigint NOT NULL,
  	EMPLOYEE_NAME nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  	GENDER varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	ACTIVE_INSTITUTE_ID bigint NULL,
);
INSERT dbo.EMPLOYEE(
  EMPLOYEE_ID,	EMPLOYEE_NAME,	GENDER, ACTIVE_INSTITUTE_ID) VALUES
(1,'KAYLING', 'FEMALE', 1001),
(2,'BLAZE', 'MALE', 3001);
2 rows affected

SELECT * FROM dbo.EMPLoYEE


EMPLOYEE_ID
EMPLOYEE_NAME
GENDER
ACTIVE_INSTITUTE_ID


1
KAYLING
FEMALE
1001

2
BLAZE
MALE
3001


CREATE PROCEDURE dbo.INSTITUTE_WISE_EMPLOYEE
@instituteId INT
AS
BEGIN
SELECT
(SELECT COUNT(e.EMPLOYEE_ID) FROM EMPLOYEE e WHERE ACTIVE_INSTITUTE_ID = @instituteId) AS TOTAL_EMPLOYEE,
(SELECT COUNT(e.EMPLOYEE_ID) FROM EMPLOYEE e WHERE ACTIVE_INSTITUTE_ID = @instituteId AND GENDER = 'FEMALE') AS FEMALE_EMPLOYEE,
(SELECT COUNT(e.EMPLOYEE_ID) FROM EMPLOYEE e WHERE ACTIVE_INSTITUTE_ID = @instituteId AND GENDER = 'MALE') AS MALE_EMPLOYEE
END;
EXEC INSTITUTE_WISE_EMPLOYEE @instituteId = 1001;


TOTAL_EMPLOYEE
FEMALE_EMPLOYEE
MALE_EMPLOYEE


1
1
0