• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1256
  • Last Modified:

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

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
Y2Kingswood
Asked:
Y2Kingswood
  • 2
1 Solution
 
jdlambert1Commented:
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
 
AaronAbendCommented:
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
 
jdlambert1Commented:
Y2Kingswood, not only is AaronAbend's query shorter, it runs much faster than mine, too.
0
 
Y2KingswoodAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now