sus with joins are wrong - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

sus with joins are wrong

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2015-03-25 : 12:04:02

I need to take a few tables and join them togehter with coutns and sums but if 1 table has many records then it counts that more then once

so

select count(c.id),count(p.id),sum(p.amount),count(l.id) from customers c left join payments p on p.customerid=c.id left join payments p on p.customerid=c.id left join letters l on l.customerid=c.id

now the problem is that if there is 5 letters it counts the one amount 5 times and gives me the wrong results

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-03-25 : 12:07:11

select count(c.id),count(p.id),sum(p.amount),count(l.id)
from customers c
left join payments p on p.customerid=c.id
left join (SELECT DISTINCT customerid FROM letters) l on l.customerid=c.id

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2015-03-25 : 12:43:16

that won't work as I want the letter count to be the total letter count not just 1 per customer

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-25 : 12:48:38

Idea:

run your query with out the counts and sums, just:

select c.id, p.id, p.amount, l.id ...

and see what you get. Likely you'll have some rows from letters multiple times. Now, figure out how to get just the rows you want. Then, count those.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-25 : 18:28:05

[code]
select c.id, max(p.id_count) as p_id_count, max(p.amount_sum) as p_amount, max(l.id_count) as l_id_count
from customers c
left join (
select customerid, count(customerid) as id_count, sum(amount) as amount_sum
from payments
group by customerid
) as p on p.customerid=c.id
left join (
select customerid, count(customerid) as id_count
from letters
group by customerid
) as l on l.customerid=c.id

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources