How can you use `AVG` and `GROUP BY` in MySQL to calculate multiple averages of a column for different groups within a table?

SQL Query with AVG and Group By

In MySQL, a query that extracts multiple averages of a column for different groups within a table can be achieved using a combination of the AVG() and GROUP BY clauses. Consider a table data_r1 with the following structure:

mysql> select id, pass, val from data_r1 limit 10;
+------------+--------------+----------------+
| id         | pass         | val            |
+------------+--------------+----------------+
| DA02959106 | 5.0000000000 | 44.4007000000 |
| 08A5969201 | 1.0000000000 | 182.4100000000 |
| 08A5969201 | 2.0000000000 | 138.7880000000 |
| DA02882103 | 5.0000000000 | 44.7265000000 |
| DA02959106 | 1.0000000000 | 186.1470000000 |
| DA02959106 | 2.0000000000 | 148.2660000000 |
| DA02959106 | 3.0000000000 | 111.9050000000 |
| DA02959106 | 4.0000000000 | 76.1485000000 |
| DA02959106 | 5.0000000000 | 44.4007000000 |
| DA02959106 | 4.0000000000 | 76.6485000000 |

To extract the desired information from this table, use the following query:

SELECT id, pass, AVG(val) AS val
FROM data_r1
GROUP BY id, pass;

This query groups the rows in the data_r1 table by both the id and pass columns, and then calculates the average value of the val column for each group. The result is a table with one row for each unique combination of id and pass, with the corresponding average value for val.

An alternative query, which achieves the same result but returns a single row for each unique id with multiple average values:

SELECT d1.id,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 WHERE d2.id = d1.id AND pass = 1) as val_1,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 WHERE d2.id = d1.id AND pass = 2) as val_2,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 WHERE d2.id = d1.id AND pass = 3) as val_3,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 WHERE d2.id = d1.id AND pass = 4) as val_4,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 WHERE d2.id = d1.id AND pass = 5) as val_5,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 WHERE d2.id = d1.id AND pass = 6) as val_6,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 WHERE d2.id = d1.id AND pass = 7) as val_7
FROM data_r1 d1
GROUP BY d1.id

This query uses nested subqueries to calculate the average values for each pass value within a specific id group. The IFNULL() function is used to ensure that null values are replaced with 0, preventing division by zero errors.

The above is the detailed content of How can you use `AVG` and `GROUP BY` in MySQL to calculate multiple averages of a column for different groups within a table?. For more information, please follow other related articles on the PHP Chinese website!