CODEWITHSUNDEEP
mysqldatabasesumunionaverage
Homer_J
Homer_J

Reputation: 3323

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.

Upvotes: 2

Views: 964

Answers (2)

Martin Smith
Martin Smith

Reputation: 452988

(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;

Upvotes: 3

Jeremy Thompson
Jeremy Thompson

Reputation: 65544

Without a JOIN, why not just use a temp table or table variable. So insert the SUMs:

INSERT INTO tempTable (SELECT COUNT(*), SUM(q1) AS q1.... FROM thotels_results WHERE brand = 'EFG' AND date = 'NOV2010' GROUP BY brand)

INSERT INTO tempTable (SELECT COUNT(*), SUM(q1) AS q1.... FROM thotels_results WHERE brand = 'XYC' AND date = 'NOV2010' GROUP BY brand)

INSERT INTO tempTable (SELECT COUNT(*), SUM(q1) AS q1.... FROM thotels_results WHERE brand = 'ABC' AND date = 'NOV2010' GROUP BY brand)

Then fetch the adverage

SELECT SUM(q1) / 3 as q1Adverage,.... From tempTable

Upvotes: 1

Related Questions