Adding up POSTGRESQL values

Question

I have a table historicos:

          historicos
id | problemas | total | data
---|-----------|-------|------------
01 |    25     |  125  | 2017-01-01
---|-----------|-------|------------
03 |    25     |  125  | 2017-03-01
---|-----------|-------|------------
04 |    25     |  125  | 2017-04-01
---|-----------|-------|------------
08 |    25     |  125  | 2017-08-01
---|-----------|-------|------------
09 |    25     |  125  | 2017-09-01
---|-----------|-------|------------
10 |    25     |  125  | 2017-10-01
---|-----------|-------|------------
12 |    25     |  125  | 2017-12-01

I need to print even months with the sum of fields problemas and total, ie, for example, need to display the sum of month 1 with month 2, month 3 with month 4, month 5 with 6 and so on...

See the Fiddle example below:

SQL FIDDLE FOR EXAMPLE

If the link fails, try opening again!

Answer 1

I believe that one way to do this would be to have the number of the month in the table of histories, more as it does not have, so it would have to be by date of beginning and end:

select 
case mes
 when 1 then (select sum(problemas), sum(total) from tbhistoricos where data between '2016-01-01' and '2016-02-28'  
else
  0
end as 1Mes,
case mes
 when 2 then (select sum(problemas), sum(total) from tbhistoricos where data between '2016-03-01' and '2016-02-28'  
else
  0
end as 2Mes
from tbhistoricos

Answer 2

If you are using postgres version 9.4 or higher, you can use the FILTER clause, which uses less code than CASE and follows the SQL standard 2003.

Below is an example:

select 
  sum(problemas) filter (where data between '2017-01-01' and '2017-02-28') as problemas_mes_1,
  sum(total) filter (where data between '2017-01-01' and '2017-02-28') as total_mes_1,
  sum(problemas) filter (where data between '2017-03-01' and '2017-04-30') as problemas_mes_2,
  sum(total) filter (where data between '2017-03-01' and '2017-04-30') as total_mes_2
from historicos;

Reference: https://www.postgresql.org/docs/9.4/static/sql-expressions.html