tay_thomp - 3 months ago 8x
SQL Question

# How to calculate averages over a range psql

I am able to get an average from my database for one year. Is there a way to effectively get this data for all the years (2017-2054) in my database without having to manually enter the date every time? Is it possible after I select the average from all the dates to put that in a new table? Thanks!

``````lcoe=# select techindex, avg(lcoe) from lcoe where year = 2017 group by techindex;
techindex        |        avg
------------------------+--------------------
Combustion Turbine     | 0.0564000003039837
Unscrubbed             | 0.0689999982714653
Offshore               |  0.119428569717067
Photovoltaic           |  0.208888891670439
EGS                    | 0.0549999997019768
Fuel Cell              |  0.115666666378578
Onshore                | 0.0587692308024718
Scrubbed               | 0.0556000009179115
Solar Thermal          |  0.134285714477301
Combined Cycle         | 0.0457142852246761
Hydrothermal           |  0.104249998694286
Hydroelectric          | 0.0765999995172024
IGCC                   | 0.0762727270749482
Distributed Generation |  0.282000001519918
Nuclear                | 0.0755000002682209
Biopower               |  0.125571429197277
(16 rows)`
``````

You can generate all combinations of the years and tech indexes and then generate the average for each combination:

``````select y.year, ti.techindex, avg(lcoe.lcoe)
from (select distinct year from lcoe) y cross join
(select distinct techindex from lco) ti left join
lcoe
on y.year = lcoe.year and ti.techindex = lcoe.techindex
group by y.year, ti.techindex;
``````

If you only want `techindex` that are in each year:

``````select yti.year, yti.techindex, avg(lcoe.lcoe)
from (select distinct year, techindex from lcoe) yti
lcoe
on yti.year = lcoe.year and yti.techindex = lcoe.techindex
group by yti.year, yti.techindex;
``````
Source (Stackoverflow)