SELECT SUM(mytable.WA) AS weightedAverage, mytable.state AS state, mytable.year AS year FROM (SELECT StateData.StateShort AS state, StateData.DataYear AS year, ((StateData.ExpEmpValue/TotalStateEmp.TotalEmp)*Gamma.Gamma) AS WA FROM StateData JOIN (SELECT SUM(StateData.ExpEmpValue) AS TotalEmp, StateData.StateShort AS state FROM StateData WHERE StateData.DataYear=2012 AND StateData.IndustryCodeLength=6 GROUP BY StateData.StateShort) AS TotalStateEmp ON StateData.StateShort=TotalStateEmp.state JOIN Gamma ON Gamma.IndustryCode=StateData.IndustryCode AND Gamma.DataYear=StateData.DataYear WHERE StateData.DataYear=2012 AND StateData.IndustryCodeLength=6) AS mytable GROUP BY mytable.state, mytable.year

Above is the average level of agglomeration by state in 2012.

How would I change the code to find the average level of Competitiveness (HHI)?

This is in SQL.