CODEWITHSUNDEEP
mysqlunion
Homer_J
Homer_J

Reputation: 3323

MySQL 'union all' not bringing back information for second select statement

EDIT: UNION and UNION ALL have the same result.

I have the following statement - I had assumed that this would bring back the following columns

I've realised that I am asking to UNION from the same table, does that matter? I need to bring back data for 'hotel_name, company, hotel_1 and q351' - is that possible?

hotel_name, company, hotel_1 and q351

(SELECT hotel_name, company, 
((AVG (q1) + AVG(q2) + AVG(q3) + AVG(q4) + AVG(q5) + AVG(q6) + AVG(q7) + AVG(q8) + AVG(q9) + AVG(q10) + AVG(q11) + AVG(q12) + AVG(q13) + AVG(q14) + AVG(q15) + AVG(q16) + AVG(q17) + AVG(q18) + AVG(q19) + AVG(q20) + AVG(q21) + AVG(q22) + AVG(q23)+ AVG(q24)+ AVG(q25) ) / 25 ) AS hotel_1
FROM thotels_respondents 
LEFT JOIN thotels_results_new ON thotels_respondents.login_id = thotels_results_new.company AND thotels_results_new.sdate = 'NOV2014' 
WHERE thotels_respondents.brand = 'ACME' AND thotels_respondents.sdate = 'NOV2014' AND completion_status = 'Not Started' 
GROUP BY hotel_name 
ORDER BY hotel_name)
UNION ALL
(SELECT hotel_name, company, 
((AVG (q1) + AVG(q2) + AVG(q3) + AVG(q4) + AVG(q5) + AVG(q6) + AVG(q7) + AVG(q8) + AVG(q9) + AVG(q10) + AVG(q11) + AVG(q12) + AVG(q13) + AVG(q14) + AVG(q15) + AVG(q16) + AVG(q17) + AVG(q18) + AVG(q19) + AVG(q20) + AVG(q21) + AVG(q22) + AVG(q23)+ AVG(q24)+ AVG(q25) ) / 25 ) AS q351
FROM thotels_respondents 
LEFT JOIN thotels_results_new ON thotels_respondents.login_id = thotels_results_new.company AND thotels_results_new.sdate = 'NOV2014' 
WHERE q35 = 1 AND thotels_respondents.brand = 'ACME' AND thotels_respondents.sdate = 'NOV2014' AND completion_status = 'Not Started' 
GROUP BY hotel_name 
ORDER BY hotel_name)

But it actually, only brings back data for:

hotel_name, company, hotel_1

It is missing q351

I can only surmise I am using UNION ALL incorrectly - can anyone point out where I am going wrong?

Upvotes: 0

Views: 50

Answers (1)

Mihai
Mihai

Reputation: 26804

Move the different condition from WHERE in the aggregate function

SELECT hotel_name, company, 
((AVG (q1) + AVG(q2) + AVG(q3) + AVG(q4) + AVG(q5) + AVG(q6) + AVG(q7) + AVG(q8) + AVG(q9) + AVG(q10) + AVG(q11) + AVG(q12) + AVG(q13) + AVG(q14) + AVG(q15) + AVG(q16) + AVG(q17) + AVG(q18) + AVG(q19) + AVG(q20) + AVG(q21) + AVG(q22) + AVG(q23)+ AVG(q24)+ AVG(q25) ) / 25 ) AS hotel_1,
((AVG (CASE WHEN q35 = 1 THEN  q1 END) + AVG(CASE WHEN q35 = 1 THEN  q2 END) + AVG(CASE WHEN q35 = 1 THEN  q3 END) + AVG(CASE WHEN q35 = 1 THEN  q4 END) + AVG(CASE WHEN q35 = 1 THEN  q5 END) + AVG(CASE WHEN q35 = 1 THEN  q6 END) + AVG(CASE WHEN q35 = 1 THEN  q7 END) + AVG(CASE WHEN q35 = 1 THEN  q8 END) + AVG(CASE WHEN q35 = 1 THEN  q9 END) + AVG(CASE WHEN q35 = 1 THEN  q10 END) + AVG(CASE WHEN q35 = 1 THEN  q11 END) + AVG(CASE WHEN q35 = 1 THEN  q12 END) + AVG(CASE WHEN q35 = 1 THEN  q13 END) + AVG(CASE WHEN q35 = 1 THEN  q14 END) + AVG(CASE WHEN q35 = 1 THEN  q15 END) + AVG(CASE WHEN q35 = 1 THEN  q16 END) + AVG(CASE WHEN q35 = 1 THEN  q17 END) + AVG(CASE WHEN q35 = 1 THEN  q18 END) + AVG(CASE WHEN q35 = 1 THEN  q19 END) + AVG(CASE WHEN q35 = 1 THEN  q20 END) + AVG(CASE WHEN q35 = 1 THEN  q21 END) + AVG(CASE WHEN q35 = 1 THEN  q22 END) + AVG(CASE WHEN q35 = 1 THEN  q23 END)+ AVG(CASE WHEN q35 = 1 THEN  q24 END)+ AVG(CASE WHEN q35 = 1 THEN  q25 END) ) / 25 ) AS q351
FROM thotels_respondents 
LEFT JOIN thotels_results_new ON thotels_respondents.login_id = thotels_results_new.company AND thotels_results_new.sdate = 'NOV2014' 
WHERE thotels_respondents.brand = 'ACME' AND thotels_respondents.sdate = 'NOV2014' AND completion_status = 'Not Started' 
GROUP BY hotel_name 
ORDER BY hotel_name

Upvotes: 1

Related Questions