How can I calculate average values for each \'pass\' for the same \'id\' and pivot the data for one row per \'id\' in SQL?

SQL Query with AVG and GROUP BY

In SQL, you may encounter a situation where you need to extract grouped averages from a table. This can be achieved using the AVG() and GROUP BY clauses.

Consider the example table:

id	pass	val
DA02959106	5.00	44.40
08A5969201	1.00	182.41
08A5969201	2.00	138.78
DA02882103	5.00	44.73
DA02959106	1.00	186.15
DA02959106	2.00	148.27
DA02959106	3.00	111.91
DA02959106	4.00	76.15
DA02959106	5.00	44.40
DA02959106	4.00	76.65

Calculating Average Values for Each Pass

To calculate the average value for each 'pass' for the same 'id', use the following query:

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

This query groups the rows by both 'id' and 'pass' and calculates the average 'val' for each group. The result would be:

id	pass	val_1
DA02959106	1.00	186.15
08A5969201	1.00	182.41
DA02882103	5.00	44.73
08A5969201	2.00	138.78
DA02959106	2.00	148.27
DA02959106	3.00	111.91
DA02959106	4.00	76.40
DA02959106	5.00	44.40

Pivoting the Data for One Row per ID

If you prefer to have one row per 'id' with multiple columns representing the average values for each 'pass', use the following 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 subqueries to extract the average values for each 'pass' and assigns them to the corresponding columns. The resulting table would be:

id	val_1	val_2	val_3	val_4	val_5	val_6	val_7
DA02959106	186.15	148.27	111.91	76.40	44.40	0	0

The above is the detailed content of How can I calculate average values for each \'pass\' for the same \'id\' and pivot the data for one row per \'id\' in SQL?. For more information, please follow other related articles on the PHP Chinese website!