MySQL Union and Average

I've no idea if I am using UNION correctly in this scenario - there may well be a better/easier way and I'm open to suggestions:

I have the following code:

SELECT COUNT(*), AVG (q1) AS q1, AVG (q2) AS q2, AVG (q3) AS q3, AVG (q4) AS q4, AVG (q5) AS q5, AVG (q6) AS q6, AVG (q7) AS q7, AVG (q8) AS q8, AVG (q9) AS q9, AVG (q10) AS q10, AVG (q11) AS q11, AVG (q12) AS q12, AVG (q13) AS q13, AVG (q14) AS q14, AVG (q15) AS q15, AVG (q16) AS q16, AVG (q17) AS q17, AVG (q18) AS q18, AVG (q19) AS q19, AVG (q20) AS q20, AVG (q21) AS q21, AVG (q22) AS q22 FROM thotels_results WHERE brand = 'EFG' AND date = 'NOV2010' GROUP BY brand
UNION
SELECT COUNT(*), AVG (q1) AS q1, AVG (q2) AS q2, AVG (q3) AS q3, AVG (q4) AS q4, AVG (q5) AS q5, AVG (q6) AS q6, AVG (q7) AS q7, AVG (q8) AS q8, AVG (q9) AS q9, AVG (q10) AS q10, AVG (q11) AS q11, AVG (q12) AS q12, AVG (q13) AS q13, AVG (q14) AS q14, AVG (q15) AS q15, AVG (q16) AS q16, AVG (q17) AS q17, AVG (q18) AS q18, AVG (q19) AS q19, AVG (q20) AS q20, AVG (q21) AS q21, AVG (q22) AS q22 FROM thotels_results WHERE brand = 'XYC' AND date = 'NOV2010' GROUP BY brand
UNION
SELECT COUNT(*), AVG (q1) AS q1, AVG (q2) AS q2, AVG (q3) AS q3, AVG (q4) AS q4, AVG (q5) AS q5, AVG (q6) AS q6, AVG (q7) AS q7, AVG (q8) AS q8, AVG (q9) AS q9, AVG (q10) AS q10, AVG (q11) AS q11, AVG (q12) AS q12, AVG (q13) AS q13, AVG (q14) AS q14, AVG (q15) AS q15, AVG (q16) AS q16, AVG (q17) AS q17, AVG (q18) AS q18, AVG (q19) AS q19, AVG (q20) AS q20, AVG (q21) AS q21, AVG (q22) AS q22 FROM thotels_results WHERE brand = 'ABC' AND date = 'NOV2010' GROUP BY brand

It outputs the following:

       q1      q2      q3   etc.                                                                                        
140 8.7714  8.8429  8.1643  8.7500  8.7571  8.9000  9.4071  9.1214  8.5714  8.7643  9.5143  8.9429  9.1643  8.9857  7.9500  8.9286  8.7000  9.0429  9.0143  8.7214  9.1214  9.3071
29   8.1724  8.2414  8.2414  7.8966  8.5862  8.5517  9.0000  8.5862  8.1724  7.9655  8.8966  8.6207  8.2414  8.3793  7.8276  8.3793  7.9310  8.4138  8.6897  8.3448  8.8621  8.5172
897 8.6009  8.5686  7.8528  8.3133  8.3423  8.6410  9.0301  8.6912  8.3233  8.3389  9.2029  8.3969  8.6856  8.5017  7.8071  8.4816  8.3512  8.6789  8.6789  8.3913  8.6388  8.8986

All I would 'like to do' is AVERAGE each of the q1, q2, q3 columns or SUM them and divide by 3.

Like I say, if there is a better way that doesn't use JOIN, that's fine with me!!!

Thanks in advance,

Homer.

This question and answers originated from www.stackoverflow.com

Question by Homer_J (12/6/2010 9:58:34 PM)

Answer

(1) You don't need to calculate the results separately and then UNION them like that. (2) I think WITH ROLLUP will probably do what you need.

SELECT COUNT(*), AVG (q1) AS q1,... 
FROM thotels_results WHERE brand in ('ABC','EFG','XYZ') AND date = 'NOV2010'
GROUP BY brand
WITH ROLLUP

But as pointed out in the comments this doesn't do the "mean-of-mean"s averaging required. The best I could come up with for that is

CREATE TEMPORARY TABLE results 
SELECT 
       brand, 
       COUNT(*) AS cnt, 
       AVG (q1) AS q1
       ...
FROM thotels_results 
WHERE brand in ('ABC','EFG','XYZ') AND date = 'NOV2010'
GROUP BY brand;

CREATE TEMPORARY TABLE results2 
SELECT cast(NULL as char), AVG(cnt), AVG(q1) 
FROM results r2;


/*MySQL doesn't allow the same temp table to be accessed twice in a UNION!*/
SELECT * FROM results r
UNION ALL
SELECT *
FROM results2;

DROP TEMPORARY TABLE results;
DROP TEMPORARY TABLE results2;

Answer by Martin Smith

Show Additional Answers >>

Find More Answers

MySQL Union and Average

Answer

MySQL Join and Union Optimization

Union with Count OR Join with Sum - MySQL

MYSQL UNION and ORDER BY not working

mysql union of groupwise maximum and range

MySQL Union, table priority and fake columns

MySQL UNION and PHP, not friends?

MySQL Range and Average

Moving average - MySQL

MySQL union and order by help

UNION and ORDER BY issue in MySQL