qods qods - 7 months ago 24
SQL Question

SQL AdventureWorks count employees by gender by city

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

Answer

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()