CODEWITHSUNDEEP
sqlsql-serverselectsql-updateaverage
Aleksander Kuś
Aleksander Kuś

Reputation: 47

Multiple SELECT followed by multiple UPDATE

I have two tables - 1st table gas_emissions, 2nd table - regiony_avg.

Table gas_emissions has columns region, region_id, data_val, year.

Table regiony_avg has columns region_id, avg_region.

There are multiple values for each region because they're calculated every year. I need to calculate AVG for each region and insert it into regiony_avg.

There are over 10 regions, what I've done is

SELECT AVG(data_val) AS AKL 
FROM gas_emissions 
WHERE region_id = 'AKL'

and then

UPDATE regiony_avg 
SET avg_region = 1999.64771428571 
WHERE region_id = 'AKL'

I did it for each of regions. However I can't see how to do it if there are for example 1000 regions. Is there any way to get AVG for all unique regions at and then insert it into regiony_avg at once?

enter image description here

Upvotes: 1

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think you just want insert . . . select:

insert into regiony_avg (region_id, avg_region)
    selet region_id, avg(data_val)
    from gas_emissions
    group by region_id;

Note: I see little reason to store this information in a table when it can easily be calculated using an aggregation query. In fact, you can add the average to each row of the original table using window functions:

select ge.*,
       avg(data) over (partition by region_id) as region_avg
from gas_emissions ge;

Upvotes: 1

Related Questions