MySQL Union Returns Inconsistent Results

Question

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.

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.

*Never* use commas in the `FROM` clause. *Always* use proper, explicit, **standard** `JOIN` syntax. — Gordon Linoff, Sep 13 '18 at 10:46

score 1 · Answer 1 · answered Sep 13 '18 at 03:42

1

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?

answered Sep 13 '18 at 03:42

Scott Noyes

348
1
9

score 1 · Accepted Answer · answered Sep 13 '18 at 04:57

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

This makes sense now - both select queries happened to return the same number of points for that student, so it did a distinct. Using the UNION ALL fixes the issue. I'll also make changes to the join structure. — boojack, Sep 13 '18 at 12:55

MySQL Union Returns Inconsistent Results

2 Answers2