MySQL Union Returns Inconsistent Results

I have a UNION statement to combine scores for students from two different tables (MySQL 5.6). It is returning inconsistent results. When I run each statement independently, they generate the right results for all students. When I combine them with the UNION, it is correct for all but one student.

Even stranger, if I remove any one of the items being added from the first query, it sums correctly for ALL records.

For instance, removing sum(ifnull(owl_tracker.pre_req_points,0)) or sum(ifnull(owl_tracker.bonus_points,0)) from the query causes it to return correct results for all students.

sum(ifnull(owl_tracker.pre_req_points,0))

sum(ifnull(owl_tracker.bonus_points,0))

select first_name, last_name, location, sum(total_points) as total_points from (
select first_name, last_name, location, (
sum(ifnull(owl_tracker.pre_req_points,0)) +
sum(ifnull(owl_tracker.bonus_points,0)) +
sum(ifnull(owl_tracker.a_points,0))) AS total_points
from products, students_products, students


Fiddle here: http://sqlfiddle.com/#!9/7ea891/1

Student A works correctly but Student B does not.

FROM

JOIN

2 Answers
2

You need to use union all, try below query and it's better if you use explicit join

http://sqlfiddle.com/#!9/7ea891/7

select first_name, last_name, location, sum(total_points)
from
(
select first_name, last_name, location, (
sum(ifnull(owl_tracker.pre_req_points,0)) +
sum(ifnull(owl_tracker.bonus_points,0)) +
sum(ifnull(owl_tracker.a_points,0))) AS total_points
from products left join students_products on products.product_id = students_products.product_id
left join students on students_products.student_id = students.unique_id
left join owl_tracker on students.unique_id = owl_tracker.student_id
where products.product_type in ('Group_Course','Full_Course') and products.test_date_id = '19' and unique_id in ('4833' ,'4956')
group by first_name, last_name, location

union all

select first_name, last_name, location,
sum(ifnull(owl.points,0)) AS total_points
from products left join students_products on
products.product_id = students_products.product_id
left join students on students_products.student_id = students.unique_id
left join owl on students.unique_id = owl.student_id
where products.product_type In ('Group_Course' ,'Full_Course') and
products.test_date_id = '19' and
unique_id in( '4833' , '4956')
group by first_name, last_name, location) t_union
group by first_name, last_name, location
order by location, last_name


By default, UNION is UNION DISTINCT, which means that any duplicated row will be filtered out in the subquery. Change that to UNION ALL. See What is the difference between UNION and UNION ALL?

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.