user5826068 user5826068 - 2 years ago 71
MySQL Question

Get average of rows with some conditions in mysql

I have 3 columns with id,level1,level2.I need to get average of level 1 and level2 with respect to id.Same Id have multiple level1 and level2.

I tried to get unique rows for id..but average i couldnt.

+--------+----------+-----------+
| id | level1 | level2 |
+--------+----------+-----------+
| 1 | 2 | 4 |
+--------+----------+-----------+
| 1 | 3 | 5 |
+--------+----------+-----------+
| 1 | 1 | 4 |
+--------+----------+-----------+
| 2 | 3 | 5 |
+--------+----------+-----------+
| 2 | 4 | 2 |
+--------+----------+-----------+
| 2 | 5 | 3 |
+--------+----------+-----------+


I need the output as like this.

+--------+----------+-------------------+
| id | avg_level1 | avg_level2 |
+--------+----------+-----------+--------
| 1 | 2 | 4.33 |
+--------+----------+-----------+-------+
| 2 | 4 | 3.33 |
+--------+----------+-----------+--------

Answer Source

SELECT ID,AVG(level1) avg_level1,AVG(level2)avg_level2 FROM TABLE GROUP BY ID

You can try above code.

Here i had used AVG mysql inbuilt function for same.

Hope this will help you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download