rks rks - 3 months ago 11
SQL Question

How can I combine two select statements into one table with two seperate columns?

New to SQL, using MS SQL Sever Management Studio with AdventureWorks sample DB:

http://elsasoft.com/samples/sqlserver_adventureworks/sqlserver.spring.katmai.adventureworks/default.htm

Trying to combine two

SELECT
statements each containing a
COUNT
of Male and Female Employees.
I can get both counts to return two rows on the same table using UNION ALL.

SELECT COUNT(HumanResources.Employee.Gender) AS 'Male Employees'
FROM HumanResources.Employee
WHERE Employee.Gender = 'M'
UNION ALL
SELECT COUNT(HumanResources.Employee.Gender) AS 'Female Employees'
FROM HumanResources.Employee
WHERE Employee.Gender = 'F';


However I'm trying to get the
COUNT
of each M/F in two separate columns. Managed to get two separate columns to appear but the counts are not there.

SELECT Set1.[Male Employees], Set2.[Female Employees]
FROM
(
SELECT COUNT(Employee.Gender) AS 'Male Employees'
FROM HumanResources.Employee
WHERE Employee.Gender = 'M'
) as Set1
INNER JOIN
(
SELECT COUNT(Employee.Gender) AS 'Female Employees'
FROM HumanResources.Employee
WHERE Employee.Gender = 'F'
) as Set2
on Set1.[Male Employees] = Set2.[Female Employees]


I feel like I'm missing something obvious..

Answer

You can do this with conditional aggregation:

SELECT SUM(CASE WHEN Employee.Gender = 'M' THEN 1 ELSE 0 END) AS 'Male Employees',
       SUM(CASE WHEN Employee.Gender = 'F' THEN 1 ELSE 0 END) AS 'Female Employees'  
FROM HumanResources.Employee

But you can do this brutal, straightforward way also:

SELECT (SELECT COUNT(HumanResources.Employee.Gender)
        FROM HumanResources.Employee
        WHERE Employee.Gender = 'M') AS 'Male Employees',
       (SELECT COUNT(HumanResources.Employee.Gender) 
        FROM HumanResources.Employee
        WHERE Employee.Gender = 'F') AS 'Female Employees'

First approach is of course preferred way.

Comments