Calculate a new table of the averages of a column with grouped by another column(s) value-CodePudding

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!

CodePudding user response：

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;

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

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

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