TMichel TMichel - 1 year ago 111
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
average for each one grouped by
field (0,1). In a multiple
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 Source

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 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download