Count male, female and total

Question

I want to count male, female and total students from Student table for a specific year specified. I wish if the result could be displayed in the form:

====================================
| Label    |  Value   |   Year     |
====================================
| Male     |   0      |   2013     |
| Female   |  23      |   2013     |
| Total    |  23      |   2013     |
====================================

The query should display 0 if there is no male/female matching for the specified year. Any idea how I can make this happen?

Thanks in advance

Answer 1

Consider the following query:

select
  max(registeredYear) as year,
  count(case when gender='Male' then 1 end) as male_cnt,
  count(case when gender='Female' then 1 end) as female_cnt,
  count(*) as total_cnt
from student
where registeredYear = 2013
group by registeredYear;

The result will be like this:

Year male_cnt female_cnt total_cnt
---- -------- ---------- ---------
2013        0         23        23

You can transform this result into the form you want. If you want to do it within a query, then you can do it like this:

with t as (
    select
      max(registeredYear) as year,
      count(case when gender='Male' then 1 end) as male_cnt,
      count(case when gender='Female' then 1 end) as female_cnt,
      count(*) as total_cnt
    from student
    where registeredYear = 2013
    group by registeredYear)
select 'Male', male_cnt as male, year from t
union all
select 'Female', female_cnt as male, year from t
union all
select 'Total', total_cnt as male, year from t
;

Answer 2

You should use:

select name, COUNT(*)as tot, 
  COUNT(case when details.gender='male' then 1 end) as male,
  COUNT(case when details.gender='female' then 1 end) as female 
  from details  group by name

Answer 3

Since you shouldn't mix grid formatting with data retrieval

SELECT
  SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) as MaleCount,
  SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) as FemaleCount,
  COUNT(*) as TotalCount
FROM student
WHERE registeredYear = 2013

Answer 4

something like this:

select 'Male' as Label, count(gender) as Value from student where gender= 'Male'
union (
select 'Female' as Label, count(gender) as Value from student where gender= 'Female' )
union (
select 'Total' as Label, count(gender) as Value from student )

Answer 5

I believe this is about as efficient as you can get with just a single pass through the student table. Simply change the year in the year CTE as needed.

with
year as 
(
  select '2013' year
),
gender as (
  select 'Male' gender
  union all
  select 'Female' gender
)
select coalesce(g.gender,'Total') "Label", 
       count(s.gender) "Value", 
       y.year "Year"
  from gender g
  cross join year y
  left join student s
    on s.gender = g.gender
   and s.year = y.year
 group by grouping sets( (g.gender, y.year), (y.year) )
 order by case g.gender when 'Male' then 1 when 'Female' then 2 else 3 end
;

A fully normalized data model will likely have both a school year and gender table, so the CTEs would not be needed. (unless you really want to return rows for years that haven't any data)

Here is is a bare-bones sqlfiddle demonstration without student id and name as they are extraneous to the problem at hand.

Answer 6

Your request seems very simple, but it has two complications. The first is that one row is a summary of the other two. This suggests using rollup or grouping sets in the query.

The second is the requirement to have values even when you have no data. This suggests the use of a "driver" subquery. Such a subquery defines all the rows in the output before assigning values. You use a driver table with left outer join.

An unstated requirement might be to only mention the year once.

The following approach to the query puts the final form together for the year. The then left joins the summary, pulling values from there if any:

with year as (
      select 2013 as Year
     )
select driver.label, coalesce(s.value, 0) as Value, driver.Year
from ((select 'Male' as label, year from year
      ) union all
      (select 'Female' as label, year from year
      ) union all
      (select 'Total' as label, year from year
      )
     ) driver left outer join
     (select coalesce(Gender, 'Total') as Gender, year.year, count(*) as value
      from Students cross join year
      group by Gender with Rollup
     ) s
     on driver.year = s.year;

This assumes that gender is represented as "Male" and "Female" and that there is a column called year in the data (without sample input or table formats one has to guess on column names and sample values).

Answer 7

Just run this query...

SELECT 
     MAX(registeredYear) as Year
    ,SUM(CASE WHEN gender = 'Male' THEN 1 END) AS Male
    ,SUM(CASE WHEN gender = 'Female' THEN 1 END) AS Female
    ,SUM(CASE WHEN gender IS NOT NULL THEN 1 ELSE 0 END) AS Total
 FROM from student
 WHERE registeredYear = 2013
 GROUP BY registeredYear;

Answer 8

Try this, assuming no nulls in Gender or RegisteredYear:

WITH AllYears AS
(
   SELECT RegisteredYear
   FROM Student
   GROUP BY RegisteredYear
)

, AllGenders AS
(
   SELECT Gender
   FROM Student
   GROUP BY Gender
)    

, AllGendersAndYears AS
(
   SELECT Gender, RegisteredYear
   FROM AllGenders, AllYears
)

SELECT Gender, RegisteredYear, CountForGenderAndYear
FROM AllGendersAndYears 
   CROSS APPLY 
   (
      SELECT COUNT(*) AS CountForGenderAndYear
      FROM Student
      WHERE Student.Gender = AllGendersAndYears.Gender
         AND Student.RegisteredYear = AllGendersAndYears.RegisteredYear
   ) countForGenderAndYear

UNION ALL

SELECT 'Total', AllYears.RegisteredYear, CountForYear
FROM AllYears
   CROSS APPLY 
   (
      SELECT COUNT(*) AS CountForYear
      FROM Student
      WHERE Student.RegisteredYear = AllYears.RegisteredYear
   ) countForYear

Answer 9

Here's another variation, using UNPIVOT. This one specifically searches for only MALE and FEMALE so it's not as flexible as my other one (since you need to hardcode each gender). But it is probably the most efficient.

WITH AllYears (RegisteredYear) AS
(
    --SELECT DISTINCT RegisteredYear
    --FROM Student

     --...OR...

    SELECT 2014
)
, GenderAndYearCounts AS
(
    SELECT RegisteredYear
        , SUM(CASE Gender WHEN 'MALE' THEN 1 ELSE 0 END) MaleCount
        , SUM(CASE Gender WHEN 'FEMALE' THEN 1 ELSE 0 END) FemaleCount
        , COUNT(*) YearCount
    FROM Student
    GROUP BY RegisteredYear
)
, GenderAndYearCountsForAllYears AS
(
    SELECT AllYears.RegisteredYear
        , ISNULL(MaleCount, 0) AS MaleCount
        , ISNULL(FemaleCount, 0) AS FemaleCount
        , ISNULL(YearCount, 0) AS YearCount
    FROM AllYears
        LEFT JOIN GenderAndYearCounts ON GenderAndYearCounts.RegisteredYear = AllYears.RegisteredYear
)

SELECT Label, Value, RegisteredYear
FROM 
(
    SELECT RegisteredYear, MaleCount AS Male, FemaleCount AS Female, YearCount AS Total
    FROM GenderAndYearCountsForAllYears
) allCounts

UNPIVOT
(
    Value FOR Label IN (Male, Female, Total)
) unpivotted

Answer 10

All the genders, then all the years, then the counts:

declare @Year int
set @Year = 2014

select labels.label,
    counts.cnt,
    @Year as registeredYear
    from 
        (select 'Male' as label, 1 as sortOrder
        union all 
        select 'Female', 2
        union all
        select 'All', 3) as labels 
   left join
       (select gender, 
        count(1) cnt
        from student
        where registeredYear = @Year
        group by gender) as counts
    on labels.label = counts.gender     
    order by labels.sortOrder

Answer 11

This worked for me. But, still it couldn't display 0 for both M and F for years where there is no data :

Select * from 
(
SELECT isnull (SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END),0) as Male,
       isnull(SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END),0) as Female,
       registeredYear as 'year'

FROM student
WHERE registeredDate.Year = 2013 //could be a variable
group by registeredYear
 ) as s

 UNPIVOT
 ( 
value FOR gender IN (Male, Female) 
 ) Sub

Answer 12

select sp.CLASS_Name , count(*) as total 
     , sum( case when si.STDNT_GENDER = 1   then 1 else 0 end )    as Male
     , sum( case when si.STDNT_GENDER = 0   then 1 else 0 end )    as Female
  from SCHOOL_PLANE sp  inner join STUDENT_INFO si 
on  sp.CLASS_ID=si.STDNT_CLASS_PLANE_ID group by sp.CLASS_Name

-------
select sp.CLASS_Name , count(*) as total 
     , sum( case si.STDNT_GENDER  when  1   then 1 else 0 end )    as Male
     , sum( case si.STDNT_GENDER  when  0   then 1 else 0 end )    as Female
  from SCHOOL_PLANE sp  inner join STUDENT_INFO si 
on  sp.CLASS_ID=si.STDNT_CLASS_PLANE_ID group by sp.CLASS_Name
------------
select sp.CLASS_Name , count(*) as total 
     , count( case when si.STDNT_GENDER = 1   then 1  end )    as Male
     , count( case when si.STDNT_GENDER = 0   then 1  end )    as Female
  from SCHOOL_PLANE sp  inner join STUDENT_INFO si 
on  sp.CLASS_ID=si.STDNT_CLASS_PLANE_ID group by sp.CLASS_Name

Answer 13

SELECT 
  (SELECT count(*) FROM patients WHERE gender='M') AS male_count, 
  (SELECT count(*) FROM patients WHERE gender='F') AS female_count;

Answer 14

Select * from (select year, count(*) as total ,sum( case when gender ='M' then 1 else 0 end ) as male , sum ( case when gender ='F' then 1 else 0 end) as female from mytable where year=2013 group by gender,year)
unpivot ( income_component_value for income_component_type in ( male,female,total ) )