Group by age group

Question

Good evening, I have to group and count how many employees I have with sex and age group. Follow the html code

      <div class="card" style="margin-top: 5%;">   
    <div class="card-header text-center">
      Quantificação de Empregados por faixas etárias
    </div>     
    <table class="table table-striped table-bordered text-center" style="height: 100%; width: 100%;">
       <<thead>
         <tr>
            <th></th>
            <th style="text-align: center;">Homens</th>
            <th style="text-align: center;">Mulheres</th>
         </tr>
       </thead>
       <tbody>         
        <tr>
          <th style="width: 30%">Maiores de 45 anos</th>
          <td>1</td>
          <td>1</td>
        </tr>
        <tr>
          <th style="width: 30%">Entre 18 e 45 anos</th>
          <td>2</td>
          <td>2</td>
        </tr>
        <tr>
          <th style="width: 30%">Menores de 18 anos</th>
          <td>2</td>
          <td>2</td>
        </tr>                         
      </tbody>
    </table>
  </div>

Example, I have to show :

Masculino Maiores que 45 anos - 2
Masculino Entre 18 e 45 anos - 5
Feminino Maiores que 45 anos - 1
Feminino Entre 18 e 45 anos - 3

Follow the sql code I’m using, but is giving error:

SELECT *, CASE (funcionario.funcionario_Sexo) 
  WHEN F COUNT(funcionario.funcionario_Sexo) AS sexoF 
  AND 
  CASE (funcionario.funcionario_Sexo) 
  WHEN M COUNT(funcionario.funcionario_Sexo) AS sexoM), 
  TIMESTAMPDIFF (YEAR, `funcionario`.`funcionario_DataNac`, CURDATE()) 
  AS idade_Funcionario 
  FROM `funcionario` WHERE `CodEmpresa` = '30' 
  GROUP BY `funcionario`.`funcionario_Sexo`

I found how to do, but this returning me the equal sum in all.

SELECT funcionario_DataNac, funcionario_Nome, funcionario_Sexo, 
    COUNT(funcionario_Sexo) AS sexo,

COUNT( CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) > 45 THEN 1 ELSE '' END) AS maiorQue,
COUNT( CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) <= 45 AND TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) >= 18 THEN 1 ELSE '' END) AS entre,
COUNT( CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) < 18 THEN 1 ELSE '' END) AS menosQue

FROM `funcionario`
    WHERE codEmpresa = 30
    GROUP BY funcionario_Sexo

Answer 1

The error is in the use of CASE; the structure should be like this:

CASE coluna_consultada 
WHEN 'valor_1' THEN 'retorno um'
WHEN 'valor_2' THEN 'retorno dois'
ELSE 'retorno tres' 
END AS valor_adicional`

Therefore, I understand that your case should be changed to return two columns instead of as is, displaying the total of people of the sexes feminine and masculine:

COUNT(CASE (funcionario.funcionario_Sexo) WHEN 'F' THEN 1 ELSE 0 END)) AS 'sexoF',
COUNT(CASE (funcionario.funcionario_Sexo) WHEN 'M' THEN 1 ELSE 0 END)) AS 'sexoM'

For the age group breakdown, how the totals will be displayed by sexo in different columns (sexoF and sexoM), this will need to be taken into account; thus, will be returned also different columns of age group by gender:

SUM(CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) > 45 AND funcionario_Sexo = 'F' THEN 1 ELSE 0 END) AS maiorQueF,
SUM(CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) > 45 AND funcionario_Sexo = 'M' THEN 1 ELSE 0 END) AS maiorQueM,
SUM(CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) <= 45 AND TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) >= 18 AND funcionario_Sexo = 'F' THEN 1 ELSE 0 END) AS entreF,
SUM(CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) <= 45 AND TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) >= 18 AND funcionario_Sexo = 'M' THEN 1 ELSE 0 END) AS entreM,
SUM(CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) < 18 AND funcionario_Sexo = 'F' THEN 1 ELSE 0 END) AS menosQueF,
SUM(CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) < 18 AND funcionario_Sexo = 'M' THEN 1 ELSE 0 END) AS menosQueM,

Note that I changed the function used in this survey to SUM(), adding up 1 (when the condition is met) or 0 (otherwise).

Answer 2

I did it. Thank you to everyone who gave me help. I’ll be leaving the code in case anyone needs it.

SELECT funcionario_DataNac, funcionario_Nome, funcionario_Sexo, 
    COUNT(funcionario_Sexo) AS sexo,

SUM( CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) > 45 THEN 1 ELSE 0 END) AS maiorQue,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) <= 45 AND TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) >= 18 THEN 1 ELSE 0 END) AS entre,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR, funcionario_DataNac, CURDATE()) < 18 THEN 1 ELSE 0 END) AS menosQue

FROM `funcionario`
    WHERE codEmpresa = 30
    GROUP BY funcionario_Sexo

Answer 3

Another solution would be to bring the records grouped by the SEX field, type:

select COUNT(*)QTD_SEXO from funcionario

where funcionario_Sexo <> '' // caso o campo aceite vazio

group by funcionario_Sexo 

order by funcionario_Sexo // gera dois registros, sempre o feminino vira primeiro