I have a table of data organized as such:
| data | groupA | groupB |
|---|---|---|
| 4.0 | 0 | 0 |
| 5.0 | 0 | 0 |
| 6.0 | 0 | 0 |
| 7.0 | 0 | 0 |
| 4.1 | 0 | 1 |
| 7.1 | 0 | 1 |
| 9.1 | 0 | 1 |
| 1.2 | 0 | 2 |
| 2.2 | 0 | 2 |
| 5.2 | 0 | 2 |
| 3.0 | 1 | 0 |
| 2.0 | 1 | 0 |
| 1.0 | 1 | 0 |
| 4.1 | 1 | 1 |
| 7.1 | 1 | 1 |
| 9.1 | 1 | 1 |
| 1.2 | 1 | 2 |
| 2.2 | 1 | 2 |
| 3.0 | 2 | 0 |
| 2.0 | 2 | 0 |
| 1.0 | 2 | 1 |
| 4.1 | 2 | 1 |
And I want to find the average of the data column for every unique pair of (groupA, groupB) like so:
| AVG(data) | groupA | groupB |
|---|---|---|
| … | 0 | 0 |
| … | 0 | 1 |
| … | 0 | 2 |
| … | 1 | 0 |
| … | 1 | 1 |
| … | 1 | 2 |
| … | 2 | 0 |
| … | 2 | 1 |
where the "…" represent the numerical averages (which I did not compute). I’m new to MySQL and I’m having an incredibly difficult time producing this result and any help would be greatly apprciated!
>Solution :
Aggregate by the two group columns and take the average of the data:
SELECT AVG(data), groupA, groupB
FROM yourTable
GROUP BY groupA, groupB
ORDER BY groupA, groupB;