How to calculate the average of a field for each unique combination of two other fields using SQL?

SQL Query with AVG and GROUP BY: Extracting Average Values for Each Field

You seek a SQL query that extracts specific information from a table, namely the average of the 'val' field for each unique 'id' and 'pass' value.

To achieve this, you can employ a simple but efficient query:

<code class="sql">SELECT id, pass, AVG(val) AS val_1
FROM data_r1
GROUP BY id, pass;</code>

This query groups the data by 'id' and 'pass,' calculating the average value of 'val' for each combination. The resulting table includes a row for every unique pair of 'id' and 'pass' values.

Alternatively, if you prefer a single row for each unique 'id' with the average values for all 'pass' values, use this query:

<code class="sql">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</code>

This query uses a nested subquery for each 'pass' value to calculate the average. It then aggregates the results by 'id,' providing a consolidated view of the data.

The above is the detailed content of How to calculate the average of a field for each unique combination of two other fields using SQL?. For more information, please follow other related articles on the PHP Chinese website!