Owais Ahmed Owais Ahmed - 1 month ago 6
SQL Question

Sql query calculating column per state average

This is my table.

Fund Industry State
1 2 NSW
1 2 ACT
1 2 VIC
1 2 NSW
1 2 ACT
1 2 VIC
1 2 NSW
1 2 ACT
1 2 VIC


I have three calculations to perform. Below is the calculated columns

Fund Industry State Column1 Column2 Column3
1 2 NSW 5 10
1 2 ACT 5 10
1 2 VIC 5 10
1 2 NSW 5 10
1 2 ACT 5 10
1 2 VIC 5 10
1 2 NSW 5 10
1 2 ACT 5 10
1 2 VIC 5 10


i have calculated
column1 as Fund*5
and
Column2 as Industry*5
. The
Column3 is the average of Column1 per state
.i,e

NSW is (5+5+5)/3
ACT is (5+5+5)/3
VIC is (5+5+5)/3


The final table will be

Fund Industry State Column1 Column2 Column3
1 2 NSW 5 10 5
1 2 ACT 5 10 5
1 2 VIC 5 10 5
1 2 NSW 5 10 5
1 2 ACT 5 10 5
1 2 VIC 5 10 5
1 2 NSW 5 10 5
1 2 ACT 5 10 5
1 2 VIC 5 10 5


I am able to calculate
column1
and
column2
but not sure how to calculate
column3
.

Select distinct Fund, Industry, State, Fund * 5 as Column1, Industry * 5 as Column2
from fundDetails


Any help would be appreciated. Thanks in advance

Answer

You can use AVG inside a window function:

SELECT Fund,
       Industry,
       State,
       Fund * 5 AS Column1,
       Industry * 5 AS Column2,
       AVG(Fund*5) OVER (PARTITION BY State) AS Column3
FROM fundDetails
Comments