Add the value of each period and put 0 in the periods that do not exist in SQL Query table

Navigation
1

I have two Income and Periods tables, within the Periods table are the months from January to February and in Income I have a value_port column in which there are records for what has been contributed in each month. ex. 

Ingresos
Periodo Valor_aporte
---------------------  
Enero   10
Enero   20
Febrero 30
Marzo   40
Marzo   50

What I want to do is a query that returns the sum of the Contribution_Value for each period, even if the Income table does not have a specific Period, when that happens it must be filled with 0, that is, with the previous example, something like this should be returned . 

Periodo Suma_Aporte
-------------------
Enero     30
Febrero   30
Marzo     90
Abril     0
Mayo      0
.....
Diciembre 0

This is the query I have so far 

 SELECT P.PERIODO,
  (SELECT SUM(ING.VALOR_APORTE)
  FROM INGRESOS ING2
  WHERE ING2.PERIODO = ING.PERIODO
  ) AS SUMA
FROM PERIODOS P
LEFT JOIN INGRESOS ING
ON p.PERIODO            =ING.PERIODO 
GROUP BY 
  p.PERIODO;

But I'm only getting the periods that exist in the Income table, like this: 

Periodo Suma_Aporte
    -------------------
    Enero     30
    Febrero   30
    Marzo     90
    
asked by Allanh 06.04.2018 в 23:02
source

1 answer

0

This maybe? 

SELECT P.PERIODO,
       NVL(SUM(ING.VALOR_APORTE),0) AS 'TOTAL_APORTE'
       FROM PERIODOS P
       LEFT JOIN INGRESOS ING
            ON P.PERIODO = ING.PERIODO
       GROUP BY P.PERIODO;

Provided that PERIODOS is a table with 12 months. We use NVL() to return 0 in case of value NULL .

    
answered by 06.04.2018 в 23:17
Notice: Undefined variable: userid in WSDL "valueofPrompt"