TMichel TMichel - 6 months ago 22
MySQL Question

GROUP BY in a Multiple SELECT

Say I have four tables with the same format:

Registered | Score
----------------------
0 | 10
1 | 5
1 | 7
0 | 6


And I want to get the
Score
average for each one grouped by
Registered
field (0,1). In a multiple
SELECT
the not grouped query that returns a single result as a row of four columns would be:

SELECT t1avg.a,t2avg.b,t3avg.c,t4avg.d FROM
(SELECT AVG(score) as a FROM table1) AS t1avg,
(SELECT AVG(score) as b FROM table2) AS t2avg,
(SELECT AVG(score) as c FROM table3) AS t3avg,
(SELECT AVG(score) as d FROM table4) AS t4avg


But how can I group it to get two results, by registered and not registered users? :

t1avg.a | t2avg.b | t3avg.c | t4avg.d | Registered
--------------------------------------------------
8 | 4.5 | 8 | 4.5 | 0
--------------------------------------------------
6 | 6.5 | 5 | 6 | 1

Answer

You need to group the subqueries and then match them up. Make sure they all have 0 and 1 values or you will need LEFT OUTER JOIN instead of inner join.

SELECT t1avg.a,t2avg.b,t3avg.c,t4avg.d, t1avg.registered FROM
(SELECT AVG(score) as a, registered FROM table1 GROUP BY registered) AS t1avg,
(SELECT AVG(score) as b, registered FROM table2 GROUP BY registered) AS t2avg,
(SELECT AVG(score) as c, registered FROM table3 GROUP BY registered) AS t3avg,
(SELECT AVG(score) as d, registered FROM table4 GROUP BY registered) AS t4avg
WHERE t1avg.registered=t2avg.registered and 
t1avg.registered=t3avg.registered and 
t1avg.registered=t4avg.registered