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 AND only to show rows, where the exampleValue is > than the GroupAvg:

| exampleGroup | exampleName | exampleValue | GroupAvg |
| --- | --- | --- | --- |
| A | name5 | 500 | 300 |
| B | name4 | 700 | 600 |
| C | name6 | 600 | 450 |

What I tried so far (with the error: `Windowed functions can only appear in the SELECT or ORDER BY clauses.`):

```
SELECT 
    exampleGroup, exampleName , exampleValue,
    AVG(exampleValue) OVER (PARTITION BY exampleGroup) AS AvgGroup
FROM exampleTable
WHERE exampleValue> AVG(exampleValue) OVER (PARTITION BY exampleGroup);

```

Answer: 
You need a subquery:

```
SELECT c.*
FROM (SELECT Continent, Name, SurfaceArea,
             AVG(SurfaceArea) OVER (PARTITION BY Continent) AS AvgPerContinent
      FROM country c
     ) c
WHERE SurfaceArea > AvgPerContinent;

```

Window functions are not allowed in the `WHERE` clause. There is a simple reason: If you have multiple `WHERE` conditions, would the window function be processed *before* the other filters or *after*? No need to answer: the syntax is not allowed.
You can not use window function in where clause. But you can use it in subquery as below:

```
select * from 
(
  SELECT 
    Continent, Name, SurfaceArea,
    AVG(SurfaceArea) OVER (PARTITION BY Continent) AS AvgPerContinent,
    AVG(SurfaceArea) OVER (PARTITION BY Continent) AS avgSurfaceArea
  FROM country
)t
where SurfaceArea>avgSurfaceArea

```

**From your example tables:**

```
create table exampleTable(  exampleGroup varchar(20),exampleName    varchar(20),exampleValue int);
insert into exampleTable values('A',    'name1' ,100);
insert into exampleTable values('B',    'name2' ,500);
insert into exampleTable values('C',    'name3' ,300);
insert into exampleTable values('B',    'name4' ,700);
insert into exampleTable values('A',    'name5' ,500);
insert into exampleTable values('C',    'name6' ,600);

select * from 
(
  SELECT 
    examplegroup, examplename, examplevalue,
    AVG(examplevalue) OVER (PARTITION BY examplegroup) AS groupavg
  FROM exampleTable
)t
where examplevalue>groupavg
GO 

```

| examplegroup | examplename | examplevalue | groupavg |
| --- | --- | --- | --- |
| A | name5 | 500 | 300 |
| B | name4 | 700 | 600 |
| C | name6 | 600 | 450 |

*db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cd870d4594a3b82d74c9a2d4d3686cbd)*