Return the average per group in each line

Question

I am using SQL Server and I have the following table exampleTable in my database:

exampleGroup	exampleName	exampleValue
A	name1	100
B	name2	500
C	name3	300
B	name4	700
A	name5	500
C	name6	600

My goal is to return the following result, where I have the average value per group in an additional column:

exampleGroup	exampleName	exampleValue	averageExampleGroup
A	name1	100	300
A	name5	500	300
B	name2	500	600
B	name4	700	600
C	name3	300	450
C	name6	600	450

What I tried so far (with mistake, because it doesn't calculate the average per group but the total average value of all columns):

SELECT 
    exampleGroup, exampleName, exampleValue,
    (SELECT AVG(exampleValue) FROM exampleTable) AS averageExampleGroup
FROM exampleTable;

Answer 1

You just need to use the OVER() clause like this:

SELECT 
    exampleGroup, exampleName, exampleValue,
    AVG(exampleValue) OVER (PARTITION BY exampleGroup) AS GroupAvg
FROM exampleTable;

This returns the average based on each exampleGroup as a new column in your result set.