Adding POSTGRESQL values

Question

Adding POSTGRESQL values

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I have a historicos table:

          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 the even months with the sum of the fields problemas and total , ie, I need to display the sum of month 1 with month 2, month 3 and month 4, month 5 with 6 and so on ...

See the% example example below:

SQL EXAMPLE FIDDLE

If the link fails, try opening again!

postgresql

asked by anonymous 20.04.2017 / 16:09

2 answers

0

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

Follow the example below:

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: link

21.04.2017 / 01:00

Is there a design pattern that can help with this problem? Export Date Type to openOffice / libreOffice Delphi

score 2 · Accepted Answer

I think one way to do this would be to have the number of the month in the historics table, more like it does not have, so it would have to be by start date and end date:

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