sum() returns incorrect value with join

Question

Here i have tables

1) users_interests

user_id    interest_id
677        12
677        14
677        13

2) answer_points

user_id    in_id    point
677        12       -1
677        12       1
677        12       1
677        14       1
678        14       1

3) interests

id    name
12    movie
13    cooking
14    music

here what i want to do is,I want output like this

interest_id    name    point
12             movie   1
13             cooking 0
14             music   1

Where user_id=677

I tried this query

select ui.interest_id,i.name,sum(a.answer_points) as total from
users_interests as ui inner join interests as i on i.id=ui.interest_id
left join answer_points as a on a.in_id=ui.interest_id
where i.user_id='677' group by a.in_id

But its not counting 1 vote. it returns 3 total for movie

Dau_uaD · Accepted Answer · 2017-02-28 12:06:16Z

1

This code will work for your requirement, Also check this below link

http://rextester.com/NQXE66482

SELECT 
J.interest_id AS 'ID',
I.NAME AS 'NAME',
CASE WHEN K.TOTAL != 0 THEN K.TOTAL
ELSE 0 END AS 'Total'
FROM
users_interests AS J
JOIN
interests AS I
ON
J.interest_id = I.id
LEFT JOIN
(SELECT 
 a.id AS 'ID', 
 a.name AS 'NAME', 
 SUM(b.point) as 'TOTAL'
 FROM 
 interests a 
 LEFT JOIN 
 answer_points b 
 ON a.id = b.in_id
 WHERE user_id = 677
 GROUP BY 1) AS K
 ON
J.interest_id = K.id
WHERE 
J.user_id = 677
GROUP BY 1
ORDER BY 1

edited Feb 28, 2017 at 12:06

answered Feb 28, 2017 at 10:55

Dau_uaD

881 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dau_uaD Over a year ago

I have updated the code, It should work now. Basically you have to group by 1

Dau_uaD Over a year ago

I have added a Where statement so that it will take only the interests of User_id 677. Before that It was taking all 9 interests. Aama chali jase code

Himanshu Bhardiya Over a year ago

thanks bro. almost chale chhe. but to pan test kari lau hu. :)

denny · Accepted Answer · 2017-02-28 09:27:44Z

1

try this

select id,name,coalesce(find_in_set(id,in_id),0) as points 
from interests i 
left join(select distinct in_id 
from answer_points a where point = 1)a 
on a.in_id = i.id cross join users_interest u 
where u.user_id = 677 group by id;

answered Feb 28, 2017 at 9:27

denny

2,2942 gold badges18 silver badges21 bronze badges

Comments

Himanshu Bhardiya · Accepted Answer · 2017-02-28 10:08:41Z

0

I just had to change JOIN little like this,

select ui.interest_id, i.name, COALESCE(SUM(a.answer_points),0) as total from users_interests as ui
join interests as i on i.id = ui.interest_id
left join answer_points as a on a.in_id = ui.interest_id AND a.user_id = ui.user_id
where ui.user_id='677'
group by a.in_id

I had to compare user_id as well in join like this AND a.user_id = ui.user_id

answered Feb 28, 2017 at 10:08

Himanshu Bhardiya

1,05716 silver badges40 bronze badges

Comments

ScaisEdge · Accepted Answer · 2017-02-28 10:28:02Z

0

You should use left join starting for interests

select i.id, sum(a.point)
from interests as i 
left users_interests as ui on ui.interest_id = i.id
left join answer_points as a on a.user_id = ui.user_id and a.in_id = i.interest_id
where i.user_id=677
group by a.id

edited Feb 28, 2017 at 10:28

answered Feb 28, 2017 at 8:03

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

1 Comment

Himanshu Bhardiya Over a year ago

interests table has no interest_id and user_id. so i think have to correct some fields. can you post answer again?

Collectives™ on Stack Overflow

sum() returns incorrect value with join

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related