Group by mysql adding null values

3

How can I add empty values in group by ?

Example:

I have a query that is organized by ages:

 CASE 
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) < 4 THEN 'Menos de 4' 
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 5 AND 9 THEN '5 a 9 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 10 AND 17 THEN '10 a 17 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 18 AND 24 THEN '18 a 24 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 25 AND 29 THEN '25 a 29 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 30 AND 39 THEN '30 a 39 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 40 AND 49 THEN '40 a 49 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 50 AND 59 THEN '50 a 59 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 60 AND 69 THEN '60 a 69 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) >= 70 THEN 'Maior que 70 Anos'
        ELSE 'SEM INFORMAÇÕES'  END 

But when values do not have results they do not appear in select, I wanted to add 0 to all of these CASE options.

My whole select for reference:

SELECT CASE 
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) < 4 THEN 'Menos de 4' 
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 5 AND 9 THEN '5 a 9 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 10 AND 17 THEN '10 a 17 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 18 AND 24 THEN '18 a 24 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 25 AND 29 THEN '25 a 29 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 30 AND 39 THEN '30 a 39 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 40 AND 49 THEN '40 a 49 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 50 AND 59 THEN '50 a 59 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 60 AND 69 THEN '60 a 69 Anos'
        WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) >= 70 THEN 'Maior que 70 Anos'
        ELSE 'SEM INFORMAÇÕES'  END , 
        COUNT(id)
        FROM pessoas GROUP BY DataNascimento
    
asked by anonymous 27.07.2017 / 16:42

2 answers

1

Unless your table lists at least one record for each of the cases listed, how you approached your problem will not produce the desired result. In this particular case, COALESCE will not help.

In your case, it seems obvious that the problem is that for some of the age brackets shown, there is no person of the same age range.

I usually resolve similar situations using a temporary table or a sub-query where I explicitly ready all categories (in this case age group). Then it's just a matter of using LEFT JOIN to get the results

I leave here an implementation using a sub-query

SELECT  Categorias.Cat,
        ISNULL(NumPessoas, 0) NumPessoas
  FROM 
  (
    SELECT 'Menos de 4'   AS Cat,       1 AS Ordenacao UNION ALL
    SELECT '5 a 9 Anos'   AS Cat,       2 AS Ordenacao UNION ALL
    SELECT '10 a 17 Anos' AS Cat,       3 AS Ordenacao UNION ALL
    SELECT '18 a 24 Anos' AS Cat,       4 AS Ordenacao UNION ALL
    SELECT '25 a 29 Anos' AS Cat,       5 AS Ordenacao UNION ALL
    SELECT '30 a 39 Anos' AS Cat,       6 AS Ordenacao UNION ALL
    SELECT '40 a 49 Anos' AS Cat,       7 AS Ordenacao UNION ALL
    SELECT '50 a 59 Anos' AS Cat,       8 AS Ordenacao UNION ALL
    SELECT '60 a 69 Anos' AS Cat,       9 AS Ordenacao UNION ALL
    SELECT 'Maior que 70 Anos' AS Cat, 10 AS Ordenacao UNION ALL
    SELECT 'SEM INFORMAÇÕES' AS Cat,   11 AS Ordenacao 
  ) Categorias
LEFT JOIN
(
    SELECT  FaixaEtaria,
            COUNT(DISTINCT id) NumPessoas
    FROM        
    (
        SELECT  id,
                CASE 
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/% Y'),CURDATE()) < 4 THEN 'Menos de 4' 
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 5 AND 9 THEN '5 a 9 Anos'
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 10 AND 17 THEN '10 a 17 Anos'
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 18 AND 24 THEN '18 a 24 Anos'
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 25 AND 29 THEN '25 a 29 Anos'
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 30 AND 39 THEN '30 a 39 Anos'
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 40 AND 49 THEN '40 a 49 Anos'
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 50 AND 59 THEN '50 a 59 Anos'
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) BETWEEN 60 AND 69 THEN '60 a 69 Anos'
                    WHEN TIMESTAMPDIFF(YEAR, STR_TO_DATE(DataNascimento, '%d/%m/%Y'),CURDATE()) >= 70 THEN 'Maior que 70 Anos'
                    ELSE 'SEM INFORMAÇÕES'  
                END FaixaEtaria, 
        FROM pessoas 
    ) iRes
    GROUP BY FaixaEtaria
) Resultados
   ON Resultados.FaixaEtaria = Categorias.Cat
ORDER BY Categorias.Ordenacao
    
28.07.2017 / 10:08
2

If "But when values do not have results they are not in the select" they are NULL values. MySQL contains a function called COALESCE .

It returns the first value not null of the query. If you can not find any, you can specify the value you want to display, for example:

WHEN TIMESTAMPDIFF(
    YEAR,
    COALESCE(STR_TO_DATE(DataNascimento, '%d/%m/%Y'), 0),
    CURDATE()
) < 4 THEN 'Menos de 4' 

In this case, you would meet this WHEN < 4 .

    
27.07.2017 / 18:00