How to Calculate Average Values for Different Pass Values in a SQL Query?

SQL Query with AVG and GROUP BY for Multiple Pass Values

You have encountered difficulties in crafting a SQL query to retrieve specific information from a table with the following structure:

+------------+--------------+----------------+
| id         | pass         | val            |
+------------+--------------+----------------+
| DA02959106 | 5.0000000000 |  44.4007000000 |
| 08A5969201 | 1.0000000000 | 182.4100000000 |
| 08A5969201 | 2.0000000000 | 138.7880000000 |
...

Your goal is to generate a query that extracts the following information:

id, AVG of 'val' for 'pass' = 1, AVG of 'val' for 'pass' = 2, etc

The desired output should resemble:

+------------+---------+---------+---------+---------+---------+---------+---------+
| id         | val_1   | val_2   | val_3   | val_4   | val_5   | val_6   | val_7   |
+------------+---------+---------+---------+---------+---------+---------+---------+
| DA02959106 | 186.147 | 148.266 | 111.905 | 76.3985 | 44.4007 | 0       | 0       |
+------------+---------+---------+---------+---------+---------+---------+---------+

Solution 1: Direct Approach

To achieve this, you can utilize the following query:

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

This query calculates the average value for each unique combination of id and pass.

Solution 2: Conditional Aggregation

If you prefer to have just one row for each id, you can employ this query:

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,
    ...
from data_r1 d1
GROUP BY d1.id

This query uses conditional aggregation to compute the average values for different pass values within each id.

The above is the detailed content of How to Calculate Average Values for Different Pass Values in a SQL Query?. For more information, please follow other related articles on the PHP Chinese website!