Subquery with Average Calculation
One approach involves using a subquery to calculate the average value of the column of interest and then comparing each row’s value against this average.
SELECT *
FROM products
WHERE revenue > (SELECT AVG(revenue) FROM products);
Output:
+----+-------------+---------+
| id | product_name| revenue |
+----+-------------+---------+
| 2 | Product B | 2200.75 |
| 4 | Product D | 2500.00 |
| 6 | Product F | 2100.50 |
+----+-------------+---------+
Explanation: This approach makes use of a subquery to compute the average revenue (AVG(revenue)) for all products in the products table. The outer query then selects all rows from products where the revenue (revenue) is greater than this calculated average. It filters out the rows, which do not meet this condition by comparing each row revenue against the overall average revenue.
Show All Rows with an Above-Average Value in MySQL
Finding All Rows with an Above-Average Value in MySQL is easy because in this article we will learn some methods to identify rows in a dataset where values exceed the dataset’s average.
Using MySQL we will discuss two approaches using subqueries with average calculations and through JOIN operations with subqueries. We’ll implement the method with the help of understanding examples and so on.