Number of men, women and total

Question

I want to calculate men, women, and students from the Student table for a specific year. I want the result to be displayed in the form:

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

The query should display 0 if there are no matches between men and women during the specified year. Any idea how I can do this?

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 convert this result to the form you want. If you want to do this in 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 should not mix grid formatting with data extraction

 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

I think this is about as effective as you can get just one pass through the students table. Just change the year of 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 is likely to have both a school year and a gender table, so CTEs are not needed. (unless you really want to return rows for years that don't have data)

Here is the sqlfiddle demon demon without the id and student name, as they are extraneous to the problem.

Answer 5

Your request seems very simple, but it has two difficulties. First, one line is a summary of the other two. This involves using rollup or grouping sets in the request.

The second requirement is to make a difference, even if you have no data. This involves using the driver subquery. Such a subquery defines all rows in the output before assigning values. You are using a driver table with left outer join .

An undefined requirement may be the mention of only one year.

The following query approach puts the final form together throughout the year. Then the left connects the summary, pulling the 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 the gender is represented as “male” and “female”, and that the data has a column named year (without entering samples or table formats, you need to guess the column names and sample values).

Answer 6

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 7

something like that:

 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 8

Try this by not allowing zeros to be "Gender" or "Registered":

 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 is another variation using UNPIVOT. This is specifically looking only for MALE and FEMALE, so it is not as flexible as my other (since you need to hard-code each gender). But this is probably the most effective.

 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 floors, then all years, then 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

It worked for me. But still he could not display 0 for 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

Choose from (select year, amount (*) as total, amount (case when gender = 'M', then 1 or 0 end) as a man, amount (case when gender = 'M', then 1 or 0 end ) as a woman from mytable where year = 2013 group by gender, year)
UNPIVOT (come_component_value for income_component_type in (male, female, total))