Complete number of query records

Question

Hello! I have a table in BD that can contain from 0 there are 12 records. (as a monthly income chart during the year. totaling 12 records)

I want to make a query in this table and I want it to return me exactly 12 records of the query.

Example: the table has only 3 records:

month 01 | 35,00

month 02 | 25,00

month 03 | 98,00

The query should return me these 3 existing months and complete the registration number with the 9 missing months.

month 01 | 35,00

month 02 | 25,00

month 03 | 98,00

• |

• |

• |

• |

• |

• |

• |

• |

Answer 1

The solution is to consult a set of pre-defined records with the months, then join the table of rents:

SELECT      M.Nome
        ,   SUM(IFNULL(Renda, 0)) AS Renda
FROM        (
                SELECT "Mes 01" AS Nome, 1 AS Mes
                UNION ALL
                SELECT "Mes 02" AS Nome, 2 AS Mes
                UNION ALL
                SELECT "Mes 03" AS Nome, 3 AS Mes
                UNION ALL
                SELECT "Mes 04" AS Nome, 4 AS Mes
                UNION ALL
                SELECT "Mes 05" AS Nome, 5 AS Mes
                UNION ALL
                SELECT "Mes 06" AS Nome, 6 AS Mes
                UNION ALL
                SELECT "Mes 07" AS Nome, 7 AS Mes
                UNION ALL
                SELECT "Mes 08" AS Nome, 8 AS Mes
                UNION ALL
                SELECT "Mes 09" AS Nome, 9 AS Mes
                UNION ALL
                SELECT "Mes 10" AS Nome, 10 AS Mes
                UNION ALL
                SELECT "Mes 11" AS Nome, 11 AS Mes
                UNION ALL
                SELECT "Mes 12" AS Nome, 12 AS Mes
          ) M
LEFT JOIN   Rendas R ON R.Mes = M.Mes
GROUP BY    M.Nome

You can see a functional example in DB Fiddle: https://www.db-fiddle.com/f/t7feMk8o95L83WjEq3w7EP/0

Answer 2

You can use the case when in Select for that. Follow an example of use:

SELECT 
   CASE WHEN valor = 0 THEN "-"
   ELSE mes
END as mes,
   CASE WHEN valor = 0 THEN " "
   ELSE valor
END AS valor
FROM tabela;

You are saying the following: when the "value" column is reset, the month will display a dash, and the value will be a blank.