Solved

# Count Where Sex = 'Male' .... ?

Posted on 2004-08-18
1,228 Views
Im trying to do a count on an employee sex in a view

I have the following table (example)

Department          Sex
IT                         M
IT                         F
IT                         M
Other                    F
Other                    M
Other                    F

i want the results as follows (example)

Department    Female        Male
IT                   1                 2
Other              2                 1
0
Question by:Y2Kingswood
• 2

LVL 15

Expert Comment

Try this:

SELECT sub1.Department, Female, Male
FROM
(SELECT Distinct Department FROM TableA) sub1
LEFT JOIN (SELECT Department, Count(*) as Female
FROM TableA
WHERE Sex = 'F'
GROUP BY Department
) sub2 ON sub1.Department = sub2.Department
LEFT JOIN (SELECT Department, Count(*) as Male
FROM TableA
WHERE Sex = 'M'
GROUP BY Department
) sub3 ON sub1.Department = sub3.Department
ORDER BY sub1.Department
0

LVL 10

Accepted Solution

AaronAbend earned 50 total points
select department, sum(case sex when 'F' then 1 else 0 end) as feMale , sum(case sex when 'M' then 1 else 0 end) as Male
group by department
0

LVL 15

Expert Comment

Y2Kingswood, not only is AaronAbend's query shorter, it runs much faster than mine, too.
0

Author Comment

i have tried both and you are right, im happy to split the points or give them all to Aaron, let me know what you think is fair
0

## Featured Post

### Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.