How to use Window Functions In SQL
Let’s Get all rows from sample_table where value in the column value is greater than the average value calculated over all the rows.
SELECT *
FROM (
SELECT *, AVG(value) OVER () AS avg_value
FROM sample_table
) AS subquery
WHERE value > avg_value;
Output:
| id | name | value | avg_value |
| --- | ---- | ----- | --------- |
| 4 | D | 25 | 24.1667 |
| 5 | E | 30 | 24.1667 |
| 6 | F | 35 | 24.1667 |
Explanation: This SQL query uses a window function to calculate the average value over all rows in sample_table. It then compares each row’s value against this average. The output displays rows where the value exceeds the calculated average, providing insights into above-average values within the dataset.
Show All Rows with an Above-Average Value in SQL
In SQL, finding All Rows of an Above Average Value is simple and is retrieved by the AVG() Function. There are various methods available in SQL that help us to easily find the Average value. In this guide, we will learn about various methods with detailed examples and their output.