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.

Show All Rows with an Above-Average Value in SQL

In SQL, displaying all rows with values exceeding the average value in a specific column involves various methods such as subqueries, joins, and window functions. These techniques allow for efficient comparison and retrieval of relevant data, ensuring precise analysis and decision-making based on above-average values within the dataset.

• Subquery in the WHERE Clause
  
• Using the Subquery with JOIN
  
• Using Window Functions

Set Up an Environment

Let consider one table called sample_table on which we will perform all the methods is shown below.

CREATE TABLE sample_table (
    id INT,
    name VARCHAR(50),
    value INT
);

INSERT INTO sample_table (id, name, value) VALUES
(1, 'A', 10),
(2, 'B', 15),
(3, 'C', 20),
(4, 'D', 25),
(5, 'E', 30),
(6, 'F', 35);

Output:

| id  | name | value |
| --- | ---- | ----- |
| 1   | A    | 10    |
| 2   | B    | 15    |
| 3   | C    | 20    |
| 4   | D    | 25    |
| 5   | E    | 30    |
| 6   | F    | 35    |

1. Subquery in the WHERE Clause

Let's Now Identify all Rows of sample_table where the value in the value column exceeds the average value calculated across all rows.

 SELECT * FROM sample_table WHERE value > (SELECT AVG(value) FROM sample_table);

Output:

| id  | name | value |
| --- | ---- | ----- |
| 4   | D    | 25    |
| 5   | E    | 30    |
| 6   | F    | 35    |

Explanation: This SQL query retrieves rows from the sample_table where the value column exceeds the average value calculated across all rows. It filters the dataset to highlight entries with values higher than the calculated average.

2. Using the Subquery with JOIN

Let's Select all rows from the sample_table where the value in the value column exceeds the average value calculated across all rows.

SELECT t1.*
FROM sample_table t1
JOIN (
    SELECT AVG(value) AS avg_value
    FROM sample_table
) t2 ON t1.value > t2.avg_value;

Output:

| id  | name | value |
| --- | ---- | ----- |
| 4   | D    | 25    |
| 5   | E    | 30    |
| 6   | F    | 35    |

Explanation: This is a query that will calculate the average value using a subquery and then will join it with the original table, folding rows that are greater than the average.

3. Using Window Functions

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.

Conclusion

Overall, In this article we have discussed about How to Show All Rows with an Above-Average Value in SQL with the understanding of various methods such as Using Subquery in the WHERE Clause, Using Window Functions and Using the Subquery with JOIN with the detailed exmaples and output along with explanations.