locked
How to display all Male and Female from join table in another table RRS feed

  • Question

  • I'm trying to make a stored procedure that takes in a department name and returns the number of Males and females in the department.

    CREATE PROCEDURE SelectEmployeeGender @name varchar(50)
    AS
    select e.gender = "Male" OR "Female",
    e.departmentID,
    d.name as departmentName
    from Department as d
    join Employees as e
    on d.departmentID = e.departmentID
    where d.name = @name

    Table structure
     
        [Department]
        (
            [departmentID] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
            [name] VARCHAR(50) NOT NULL,
            [costCentreID] INT FOREIGN KEY REFERENCES CostCentre(costCentreID)
        );
     
     
     
        [Employees]
        (
            [employeeNo] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
            [lastName] VARCHAR(50) NOT NULL,
            [firstName] VARCHAR(50) NOT NULL,
            [gender] CHAR NOT NULL,
            [IDNumber] VARCHAR(20) NOT NULL,
            [salaryLevelID] INT,
            [departmentID] INT FOREIGN KEY REFERENCES Department(departmentID)
        );

    Wednesday, June 3, 2020 5:14 PM

Answers

  • Try this if your gender data like 'F' or 'M':

    CREATE PROCEDURE SelectEmployeeGender @name varchar(50)
    AS
    select Sum(Case when e.gender ='M' then 1 else 0 end) as MaleCount
    ,Sum(Case when e.gender ='F' then 1 else 0 end) as FemaleCount,
    e.departmentID,
    d.name as departmentName
    from Department as d
    join Employees as e
    on d.departmentID = e.departmentID
    where d.name = @name
    Group by e.departmentID,d.name

    • Marked as answer by LetMeDev Wednesday, June 3, 2020 5:52 PM
    Wednesday, June 3, 2020 5:50 PM

All replies

  • Can you provide some sample data and your expected result? Thanks.

    Are you using SQL Server?

    Wednesday, June 3, 2020 5:30 PM
  • I'm using the SQL Server which is included in Visual Studio 2019.. LocalDB

    The expected result should be a list of male and female in the inputted department.


    • Edited by LetMeDev Wednesday, June 3, 2020 5:38 PM
    Wednesday, June 3, 2020 5:38 PM
  • Try this if your gender data like 'F' or 'M':

    CREATE PROCEDURE SelectEmployeeGender @name varchar(50)
    AS
    select Sum(Case when e.gender ='M' then 1 else 0 end) as MaleCount
    ,Sum(Case when e.gender ='F' then 1 else 0 end) as FemaleCount,
    e.departmentID,
    d.name as departmentName
    from Department as d
    join Employees as e
    on d.departmentID = e.departmentID
    where d.name = @name
    Group by e.departmentID,d.name

    • Marked as answer by LetMeDev Wednesday, June 3, 2020 5:52 PM
    Wednesday, June 3, 2020 5:50 PM
  • Hi LetmeDev ,

    You can use the below query in your stored procedure , Please format it as needed -


    ; with Dept_MCnt as (
                            select departmentID  ,count(*) as Mcnt
          from [Employees]
          where gender='male'
          group by departmentID

    ),
    with Dept_FCnt as (
                            select departmentID  ,count(*) as Fcnt
          from [Employees]
          where gender='female'
          group by departmentID

    )

    select d.name [dept name ],mc.Mcnt [male cnt],fc.Fcnt [female cnt]
    from department d left outer join Dept_MCnt mc on d.departmentID=mc.departmentID left outer join Dept_FCnt fc on d.departmentID=fc.departmentID
    where d.name=SP_input

    Thursday, June 4, 2020 5:22 AM