SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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')

7 rows affected

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


Sector
average


ENERGY
7.000000

IT
5.000000

OIL
8.500000


select t.Stocks, s.average, t.Sector
from table1 t
inner join (
  select Sector, AVG(Returns) as 'average'
  from table1 
  group by Sector
) s on s.Sector = t.Sector
order by Sector


Stocks
average
Sector


Citiz
7.000000
ENERGY

Utility
7.000000
ENERGY

MS
5.000000
IT

APPL
5.000000
IT

GEICO
5.000000
IT

PETRO
8.500000
OIL

BP
8.500000
OIL

Stocks	average	Sector
Citiz	7.000000	ENERGY
Utility	7.000000	ENERGY
MS	5.000000	IT
APPL	5.000000	IT
GEICO	5.000000	IT
PETRO	8.500000	OIL
BP	8.500000	OIL