Group by age group

Navigation
0

Good evening, I have to group and count how many employees I have with gender 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

Here is the sql code I'm using, but this 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 figured it out, but it's returning me the same 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

    
asked by anonymous 21.12.2018 / 03:02

3 answers

5

The error is in CASE ; the structure should look like this: 

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

So, I understand that your case should be changed to return two columns instead of how it is, displaying the total of people of the female and male : 

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 division by age group, how the totals by sexo will be displayed in different columns ( sexoF and sexoM ), this will need to be taken into account; thus, different columns of age range by sex should also be returned: 

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,

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

    
21.12.2018 / 12:17
1

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
    
21.12.2018 / 12:35
1

I did it. Thanks to all who have given me help. I'll be leaving the code if 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

    
21.12.2018 / 14:21
How do I prevent the object from exceeding the screen boundaries? Functions in JavaScript sharing information? [duplicate]