SQL query with avg and group by
average, group-by, mysql, sql
Solution
If I understand what you need, try this:
SELECT id, pass, AVG(val) AS val_1
FROM data_r1
GROUP BY id, pass;
Or, if you want just one row for every id, this:
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
Problem
I have some problems with writing a SQL query for MySQL. I have a table 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 | ``` I want to create a query that extracts the following information from the table: ``` id, AVG of 'val' for 'pass' = 1, AVG of 'val' for 'pass' = 2, etc ``` The result of the query should look like this: ``` +------------+---------+---------+---------+---------+---------+---------+---------+ | 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 | +------------+---------+---------+---------+---------+---------+---------+---------+ ``` with more rows for each unique 'id', of course. I already tried some queries like ``` SELECT id, pass, AVG(val) AS val_1 FROM data_r1 WHERE pass = 1 GROUP BY id; ``` This returns the correct result, but I have to expand it with results for the other possible values of 'pass' (up to 7) I tried to use a nested SELECT within AVG but this didn't work because I didn't figure out how to correctly limit it to the current 'id'. I then created Views to represent the result of each query for 'pass' = 1, 'pass' = 2, etc. But for most ids the highest value for 'pass' is 5. When using JOIN queries to get the final result from the views I received an empty result set, because some of the Views are empty / don't have values for a specific 'id'. Any ideas?