Help in joining three tables for COUNT
-
Hi,
I have three tables - clients, messages, orders. clientid is the primary key for clients and clientid is present in both messages and orders as foreign key. I want to get clientid, messagecount, and ordercount, that is, for each client I want to get number of messages posted by the client and number of orders placed by the client.
To get clientid, messagecount I do this:
select a.clientid, count(b.clientid) as messagecount
from clients a left outer join messages b on a.clientid = b.clientid
group by a.clientid
To get clientid, ordercount I do this:
select a.clientid, count(b.clientid) as ordercount
from clients a left outer join orders b on a.clientid = b.clientid
group by a.clientid
But I am not able to get all three clientid, messagecount, ordercount in a single query. This is what I tried. But it is not giving me the correct answer.
select a.clientid, count(b.clientid) as messagecount, count(c.clientid) as ordercount
from clients a
left outer join messages b on a.clientid = b.clientid
left outer join orders c on a.clientid = c.clientid
group by a.clientid
Can any one tell me how to do it right?
Thanks.
-
Here are the results for the three queries.
clientid, messagecount
12001
12012
12020
12063
12331
clientid, ordercount
12000
12018
12020
12061
12330
clientid, messagecount, ordercount
120010
12011616
120200
120633
123310
And this is what I should get
clientid, messagecount, ordercount
120010
120128
120200
120631
123310
Thanks.
-
Either of these methods should work. Somebody may post an even better way too.
select a.clientid
, m.messagecount
, o.ordercount
from clients as a
join (select a.clientid,
count(b.clientid) as messagecount
from clients a left outer join messages b on a.clientid = b.clientid
group by a.clientid) as m
on a.clientid = m.clientid
join (select a.clientid,
count(b.clientid) as ordercount
from clients a left outer join orders b on a.clientid = b.clientid
group by a.clientid) as o
on a.clientid = o.clientid
select a.clientid
, (select count(clientid)
from messages m
where m.clientid = a.clientid) as messagecount
, (select count(clientid)
from orders o
where o.clientid = a.clientid) as ordercount
from clients a
Hope this helps
-
Is this executed on a sql2000 sp3a multiproc box ?
If yes : did you install the hotfix for count(*) ?
Microsoft Knowledge Base article number Q814509
"FIX: A Parallel Query with a COUNT Aggregate Function May Return Unexpected Results"
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply