SQL AdventureWorks count employees by gender by city

Question

I want to count the cities by gender, like this;

City    GenderFCount  GenderMCount
Redmond 10             20

Here is my query gets city and gender in AdventureWorks database

select Gender,City from HumanResources.Employee as t1
    inner join HumanResources.EmployeeAddress as t2
    on t1.EmployeeID = t2.EmployeeID
    inner join Person.Address as t3
    on t2.AddressID = t3.AddressID

If it is possible could you show the solution in many ways, like "PIVOT", by sql function(UDF), Stored Procedure or other ways.

thanks

A suggestion: if you use aliases that are abbreviations of your tables, it might make it easier to read, e.g., hre for HumanResources.Employee. I'm sure there are lots of people who do it different ways. But for me, it helps keep the confusion level down. Also, using those aliases in the select statement--even when they're technically not needed--can remind you what table you pulled it from. — DaveX, Apr 20 '16 at 15:28

SQLMenace · Accepted Answer · 2010-08-22T10:42:58.370

Here is the PIVOT query, you can dump that in a stored procedure or udf

select City, F as GenderFCount, M as GenderMCount
 from(
select Gender,City
from HumanResources.Employee as t1
    inner join HumanResources.EmployeeAddress as t2
    on t1.EmployeeID = t2.EmployeeID
    inner join Person.Address as t3
    on t2.AddressID = t3.AddressID
    ) AS pivTemp
PIVOT
(   count(Gender)
    FOR Gender IN ([F],[M])
) AS pivTable

Example of the UDF

CREATE FUNCTION fnPivot()
RETURNS TABLE

AS
RETURN (
select City, F as GenderFCount, M as GenderMCount
 from(
select Gender,City
from HumanResources.Employee as t1
    inner join HumanResources.EmployeeAddress as t2
    on t1.EmployeeID = t2.EmployeeID
    inner join Person.Address as t3
    on t2.AddressID = t3.AddressID
    ) AS pivTemp
PIVOT
(   count(Gender)
    FOR Gender IN ([F],[M])
) AS pivTable
)
GO

now you can call it like this

 SELECT * FROM dbo.fnPivot()

DaveX · Answer 2 · 2016-04-20T15:22:42.300

Here it is using a CTE, embedded in a procedure. Now, I'm using AdventureWorks 2012, because that's all I have. But the concept is the same.

    USE [AdventureWorks]
    GO
    /****** Object:  StoredProcedure [dbo].[GenderCountbyCity]    Script Date: 4/20/2016 9:07:04 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[GenderCountbyCity]

    AS

    BEGIN

    ;WITH EmpF
            AS (
                    SELECT pa.City, hre.Gender, COUNT(hre.Gender) AS CountF
                            FROM Person.BusinessEntityAddress pbea
                                    JOIN Person.Address pa
                                            ON pbea.AddressID = pa.AddressID
                                    JOIN HumanResources.Employee hre
                                            ON pbea.BusinessEntityID = hre.BusinessEntityID
                            WHERE hre.Gender = 'F'
                            GROUP BY pa.City, hre.Gender
                    ),

    EmpM
            AS (
                    SELECT pa.City, hre.Gender, COUNT(hre.Gender) AS CountM
                            FROM Person.BusinessEntityAddress pbea
                                    JOIN Person.Address pa
                                            ON pbea.AddressID = pa.AddressID
                                    JOIN HumanResources.Employee hre
                                            ON pbea.BusinessEntityID = hre.BusinessEntityID
                            WHERE hre.Gender = 'M'
                            GROUP BY pa.City, hre.Gender
                    )

    SELECT COALESCE(EmpF.City,EmpM.City) AS City, COALESCE(EmpF.CountF,0) AS GenderFCount, COALESCE(EmpM.CountM,0) AS GenderMCount
            FROM EmpF
                    FULL JOIN EmpM
                            ON EmpF.City = EmpM.City
            ORDER BY COALESCE(EmpF.City,EmpM.City)

    END

If you want to create, rather than alter, a procedure, just change "ALTER" to "CREATE". Then refresh your list of stored procedures and you can modify it from there. After that, the "CREATE" will automatically show "ALTER" and any changes will be saved when you hit F5, if it is successful. Then you can type EXEC dbo.GenderCountbyCity (or whatever your name is) [or just right-click the procedure and choose Execute Stored Procedure] and you will get the results.

SQL AdventureWorks count employees by gender by city

2 Answers2