Количество мужчин, женщин и всего

Я хочу рассчитать мужчин, женщин и учащихся из таблицы Студентов в течение определенного года. Я хочу, чтобы результат можно было отобразить в форме:

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

Запрос должен отображать 0, если нет совпадений между мужчинами и женщинами в течение указанного года. Любая идея, как я могу это сделать?

Заранее спасибо

Ответы

Ответ 1

Рассмотрим следующий запрос:

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;

Результат будет таким:

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

Вы можете преобразовать этот результат в форму, которую вы хотите. Если вы хотите сделать это в запросе, то вы можете сделать это так:

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
;

Ответ 2

Вы должны использовать:

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

Ответ 3

Поскольку вы не должны смешивать форматирование сетки с извлечением данных

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

Ответ 4

Я считаю, что это примерно так же эффективно, как вы можете получить всего один проход через таблицу учеников. Просто измените год в году CTE по мере необходимости.

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
;

Полностью нормализованная модель данных, вероятно, будет иметь как школьный год, так и гендерную таблицу, поэтому CTE не нужны. (если вы действительно не хотите возвращать строки в течение многих лет, у которых нет данных)

Вот демон демонстраций sqlfiddle без идентификатора и имени ученика, поскольку они посторонние к проблеме.

Ответ 5

Ваш запрос кажется очень простым, но он имеет две сложности. Во-первых, одна строка представляет собой резюме двух других. Это предполагает использование rollup или grouping sets в запросе.

Второе требование - иметь значения, даже если у вас нет данных. Это предполагает использование подзапроса "драйвер". Такой подзапрос определяет все строки на выходе перед назначением значений. Вы используете таблицу драйверов с left outer join.

Неустановленным требованием может быть упоминание только одного года.

Следующий подход к запросу ставит окончательную форму вместе в течение года. Затем левый соединяет сводку, вытягивая значения оттуда, если таковые имеются:

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;

Это предполагает, что пол представлен как "мужчина" и "женщина", и что в данных есть столбец с именем year (без ввода образцов или табличных форматов нужно угадывать имена столбцов и значения выборки).

Ответ 6

Просто запустите этот запрос...

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;

Ответ 7

что-то вроде этого:

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 )

Ответ 8

Попробуйте это, не допуская нулей в "Пол" или "Зарегистрированный":

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

Ответ 9

Вот еще одна вариация, использующая UNPIVOT. Это специально ищет только MALE и FEMALE, поэтому он не такой гибкий, как мой другой (так как вам нужно жестко кодировать каждый пол). Но это, вероятно, самый эффективный.

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

Ответ 10

Все полы, затем все годы, затем подсчеты:

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

Ответ 11

Это сработало для меня. Но все же он не мог отображать 0 для M и F в течение лет, где нет данных:

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

Ответ 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

Ответ 13

Выбрать из (выберите год, количество (*) как общее, сумма (случай, когда пол = 'M', затем 1 или 0 конец) в качестве мужчины, сумма (случай, когда пол = 'F', затем 1 или 0 конец) как женщина из mytable где год = 2013 группа по полу, год)
UNPIVOT (come_component_value для дохода_компонента_типа в (мужчина, женщина, всего) )