CODEWITHSUNDEEP
postgresqlsumifs
otterdog2000
otterdog2000

Reputation: 363

Sumif in Postgresql between two tables

These are my two sample tables.

table "outage" (column formats are text, timestamp, timestamp)

+-------------------+----------------+----------------+
| outage_request_id |  actual_start  |   actual_end   |
+-------------------+----------------+----------------+
| 1-07697685        | 4/8/2015 4:48  | 4/8/2015 9:02  |
| 1-07223444        | 7/17/2015 4:24 | 8/01/2015 9:23 |
| 1-07223450        | 2/13/2015 4:24 | 4/29/2015 1:03 |
| 1-07223669        | 4/28/2017 9:20 | 4/30/2017 6:58 |
| 1-08985319        | 8/24/2015 3:18 | 8/24/2015 8:27 |
+-------------------+----------------+----------------+

and a second table "prices" (column format is numeric, timestamp)

+-------+---------------+
| price |     stamp     |
+-------+---------------+
| -2.31 | 2/1/2018 3:00 |
| -2.35 | 2/1/2018 4:00 |
| -1.77 | 2/1/2018 5:00 |
| -2.96 | 2/1/2018 6:00 |
| -5.14 | 2/1/2018 7:00 |
+-------+---------------+

My Goal: To sum the prices in between the start and stop times of each outage_request_id.

I have no idea how to go about properly joining the tables and getting a sum of prices in those outage timestamp ranges.

Upvotes: 1

Views: 249

Answers (1)

Hambone
Hambone

Reputation: 16397

I can't promise this is efficient (in fact for very large tables I feel pretty confident it's not), but this should notionally get you what you want:

select
  o.outage_request_id, o.actual_start, o.actual_end,
  sum (p.price) as total_price
from
  outage o
  left join prices p on
    p.stamp between o.actual_start and o.actual_end
 group by
   o.outage_request_id, o.actual_start, o.actual_end

Upvotes: 2

Related Questions