SQL query with avg and group by

Question

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?

You have 3 records with pass = 5, all with different ids. Which of these id do you want? Please post the output you would expect for the data you provided. — Quassnoi
– Quassnoi, Commented May 22, 2012 at 13:02
Does your AVG value have to be in columns or can your desired query return as many rows as many different pass values are there? — Grzegorz W
– Grzegorz W, Commented May 22, 2012 at 13:06
I edited my question and added a table how I expect my result. I have to feed the result on to another program, so there should be one id per row. — theFen
– theFen, Commented May 22, 2012 at 13:15

Marco · Accepted Answer · 2012-05-22 13:28:57Z

58

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

edited May 22, 2012 at 13:28

answered May 22, 2012 at 13:04

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

theFen Over a year ago

Marco, I just tried your solution and it seems like it does exactly what I wanted. Didn't knew that you could reference from an 'inner' SELECT to the outer one. Thank you so much!

JJJ · Accepted Answer · 2018-11-02 08:02:13Z

10

As I understand, you want the average value for each id at each pass. The solution is

SELECT id, pass, avg(value) FROM data_r1
GROUP BY id, pass;

edited Nov 2, 2018 at 8:02

JJJ

33.2k20 gold badges95 silver badges103 bronze badges

answered Nov 2, 2018 at 7:43

Cristian Iordache

1031 silver badge5 bronze badges

Comments

sadiya khan · Accepted Answer · 2022-11-24 12:40:48Z

0

I am assuming you have two tables with data similar to below: DW

id	sensor_id	amount
1	1	100
2	1	200
3	2	300

Sensors

id	name
1	sensor1
2	sensor2

And you want following result:

ID	AVG_rows_sensor
1	150
2	300

MySQL query to receive above results:

SELECT AVG(amount) AS AVG_rows_sensor FROM DW GROUP BY sensor_id;

edited Nov 24, 2022 at 12:40

answered Nov 24, 2022 at 12:38

sadiya khan

11 bronze badge

Collectives™ on Stack Overflow

SQL query with avg and group by

3 Answers 3

1 Comment

Comments

I am assuming you have two tables with data similar to below: DW

Sensors

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

I am assuming you have two tables with data similar to below: DW

Sensors

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related