DEV Community

Cover image for SQL Tip - Weird Behaviour of SUM or any operator When joining more than 2 tables
Jitesh Dhamaniya
Jitesh Dhamaniya

Posted on • Updated on

SQL Tip - Weird Behaviour of SUM or any operator When joining more than 2 tables

When joining more than two tables and you want to do some operator thing such as SUM from one table column, you have to make sure joining table query return only one result, else you would find some weird behaviour such as concat instead of sum.

Have a look on this query

select sum(payments.amount), users.id, channels.channel_name , channels.channel_id from users
left join payments on payments.user_id = users.id 
left join channels on channels.user_id = users.id and channels.id = (select id from channels where channels.user_id = users.id limit 0,1) 
group by users.id
Enter fullscreen mode Exit fullscreen mode

if i don't add

channels.id = (select id from channels where channels.user_id = users.id limit 0,1) 
Enter fullscreen mode Exit fullscreen mode

when joining channels table, it might fetch more than one results of channels for that user and it would make sql to behave weirdly and it ends up concating payments.amount instead of sum(payments.amount).

Discussion (1)

Collapse
snorkypie profile image
Steeve Lennmark

It's hard to help when you don't give a real example. You left join channels but don't use it so that join is unnecessary in this case. You basically fetch a random channel connected to the user in this case which is a bit weird in most cases :-)