Query Sql to bring data if they exist or not according to the relationship

Question

Query Sql to bring data if they exist or not according to the relationship

Navigation

#1 by (1 votes)

0

I have all the months and in each month, there is some data (Enrollment), given the case that there is no data in that month I want that month to appear with a zero. This is the query I have:

Select meses.mes as MES, 
       SUM(cantidad) as MAT 
       FROM vista_matriculas_todas 
       join meses 
            on meses.id = vista_matriculas_todas.mes_corte 
       where vista_matriculas_todas.ano_inf = 2017 
       group by vista_matriculas_todas.mes_corte,
                 meses.mes, 
                 vista_matriculas_todas.mes_corte

For example, I want to get this result:

Enero       174738
Febrero     0
Marzo       10344
Abril       0
Mayo        0
Junio       0
Julio       0
Agosto      0
Septiembre  0
Octubre     0
Noviembre   0
Diciembre   0

sql postgresql

asked by Andrés Cantillo 05.06.2018 в 20:53

source

1 answer

How to get a footer to stay in the background without exceeding other elements? Group query without losing different records

score 1 · Accepted Answer

If in meses you have the 12 possible months of a year, this is what you could do:

Select m.mes                            as MES, 
       SUM(coalesce(vmt.cantidad,0))    as MAT 
       FROM meses m
       LEFT JOIN vista_matriculas_todas vmt
            on m.id = vmt.mes_corte 
            and vmt.ano_inf = 2017 
       GROUP BY  m.mes

Note that instead of doing a simple JOIN (inner) we make a LEFT this assures us that independently that we have records for that month in vista_matriculas_todas , the month will continue to appear
The only thing left to do is if you do not have records in a month return 0 using SUM(coalesce(vmt.cantidad,0))