Group by mysql adding null values

Asked

Viewed 99 times

3

How can I add empty values in group by?

Example:

I have an appointment 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 the values have no results they do not appear in select, I wanted to add 0 in all these options from CASE.

My select all 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
mysql sql

2 answers

2

If "But when values have no results they are not in select" are values NULL. Mysql contains a function called COALESCE.

It returns the first value not null of the query. If there is none you can specify the value you wanted to show, for example:

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

In this case I would answer this WHEN < 4.

inserir a descrição da imagem aqui

  • It helped me a lot was exactly what I was looking for, I forgot about the coalesce, let me ask you and in the case of the case, how can I use the coalesce?

  • I think there is something wrong because I use Count(id) and it does not count as 0 the values that do not appear in the query

  • In fact everything he does not find will be set by the coalition as 0. Following the logic will never fall into the Else, for the first WHEN is < 4. If you still fall in ELSE it would be interesting to check record by records that are falling in this condition

  • I have records with 99/99/9999, so I need that when it has no value < 4 for example it fill 0 and is not doing it

  • The correct thing would be to treat this information via bank change, because, as you well know there is no such date. Change these values to null.

  • my biggest problem is not this, but when it has no value it does not put as 0

  • Still remains data formatting problem in the database. At the time of saving you need to have a treatment, a pattern, to apply when the data to be saved come empty. It still remains the case to set as null.

  • Even though I set as null still not showing 0 when it does not have value

  • I took the test in the bank and it worked. I added the image in the reply to see. If you have changed the value no, the database will understand as a null string not a null field. Apply the change via the UPDATE command and set the value to null.

  • My problem is that this null does not come, I want to fill in the values that do not come with these options EX: table persons id 1, Datanascimento 10/10/2015 id 2, Datanascimento 20/02/2000 not going to appear the other options only <4 and 10 to 17 Years I want to show all these options and the cont(id) 0

  • The answer does not address the @Guilhermefreire problem. Using COALESCE will not produce the desired result.

Show 6 more comments

1


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

In his case it seems obvious that the problem is that, for some of the age groups presented, there is not a person of age in the respective range.

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

I leave an implementation here 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

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.