Using SQL calculate average value on each sector

Question

Can you please help me , on how to calculate the average value of the returns on each sector.

**Excepted Output **

Stocks	Returns	Sector
MS	5.0	IT
Apple	5.0	IT
GEICO	5.0	IT
PETRO	8.5	OIL
BP	8.5	OIL
Citiz	7.0	ENERGY
Utility	7.0	ENERGY

This is what I have tried - But I don't see the expected result

create table table1
(
Stocks varchar(20),
Returns decimal(10,2),
Sector varchar(100)
)

INSERT INTO table1 (Stocks,Returns,Sector) VALUES ('MS','2','IT')
INSERT INTO table1 (Stocks,Returns,Sector) VALUES ('APPL','5','IT')
INSERT INTO table1 (Stocks,Returns,Sector) VALUES ('GEICO','8','IT')
INSERT INTO table1 (Stocks,Returns,Sector) VALUES ('PETRO','10','OIL')
INSERT INTO table1 (Stocks,Returns,Sector) VALUES ('BP','7','OIL')
INSERT INTO table1 (Stocks,Returns,Sector) VALUES ('Citiz','10','ENERGY')
INSERT INTO table1 (Stocks,Returns,Sector) VALUES ('Utility','4','ENERGY')

select Stocks,AVG(Returns) as 'Returns',Sector from table1 group by Stocks,Returns,Sector

Answer 1

You need a Group Aggregate:

Select
   Stocks
  ,AVG(Returns) OVER (PARTITION BY Sector) AS 'Returns'
  ,Sector 
from table1