CODEWITHSUNDEEP
mysqlsql
Ayrton
Ayrton

Reputation: 2303

Sum returns wrong value when joining same table twice

I have a parent and a child table, and the child table can reference itself via a foreign key assigned to the ref_id column.

I am trying to execute a sum which will return the parent id (parent_id) and the sum of the value column of its children, plus the sum of the value column on rows where the value referenced in ref_id points to a child of the current parent. On the other hand, rows where ref_id is not null should not be added to the sum of their own parent.

However, when I execute the sum it adds the value of each referenced child times the amount of times referenced. For example, if child of id 1 and value 11.35 is referenced three times, 34.05 is added to the sum instead of 11.35.

This is my structure:

create table parent(
  parent_id int auto_increment primary key
);

create table child(
  child_id int auto_increment primary key,
  parent_id int not null,
  value decimal(19,2) not null,
  ref_id int null,
  foreign key (parent_id) references parent (parent_id),
  foreign key (ref_id) references child (child_id)
);

And this is my sample data:

parent: (1), (2)

child:
    (1, 1, 11.35, null),
    (2, 1, 38.37, null),
    (3, 1, 65.46, null),
    (4, 2, 289.42, 1),
    (5, 2, 978.35, 1),
    (6, 2, 1669.19, 1)

When I execute this query:

select p.parent_id as parent_id, (ifnull(sum(c1.value), 0) + ifnull(sum(c2.value), 0)) as total
from parent as p
left join child as c1 on (p.parent_id = c1.parent_id and c1.ref_id is null)
left join child as c2 on (c1.child_id = c2.ref_id)
group by p.parent_id;

I expect this result:

parent_id: 1, total: 3052.14
parent_id: 2, total: 0.00

Instead, I get this result:

parent_id: 1, total: 3074.84
parent_id: 2, total: 0.00

Why? And how do I fix it?

Edit: Here's a fiddle with my issue: http://sqlfiddle.com/#!9/a7fedf/1/0

Upvotes: 2

Views: 205

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

The value 11.35 is multiplexed three times for c1.child_id = c2.ref_id matching condition in your case, and difference 34.05 is due to this. Consider seperately joining those two tables twice and combine by using union :

select sum(q.val) from
(
select (ifnull(c2.value, 0) ) as val
  from child as c1 
  left join child as c2 on c1.child_id = c2.ref_id  
union all
select ( ifnull(c.value, 0))
  from parent as p 
  left join child as c on p.parent_id = c.parent_id and c.ref_id is null 
) q

Demo

Upvotes: 1

Krishan Kumar
Krishan Kumar

Reputation: 403

try to get sum of distinct values. If did not work then let me know i will provide you the next solution as you know one issue has many solutions before a final solution.

Upvotes: 0

Related Questions