MySQL Forums
Forum List  »  Newbie

Problem with count
Posted by: Shawn G
Date: April 03, 2005 06:22AM

Hi.
I have 2 different sql strings where I use count:
select
count(a.prospect)
from
tbl1 as a
where
a.prospectuser = 10
group by
a.prospect

select
count(b.sale)
from
tbl1 as b
where
b.saleuser = 12
group by
b.sale

The first sql returns 4 and the second returns 1. I've tried creating one sql that will return both these results, but so far I haven't been able to. This is what I have:

select
count(a.prospect),
count(b.sale)
from
tbl1 as a
LEFT JOIN tbl1 as b ON b.id = a.id
where
a.prospectuser = 10 and
b.saleuser = 12
group by
a.prospect,
b.sale

This sql returns count(a.prospect) = 1 and count(b.sale) = 1 when it should return 4 and 1. What am I doing wrong?

Thanks,
Shawn

Options: ReplyQuote


Subject
Written By
Posted
Problem with count
April 03, 2005 06:22AM
April 03, 2005 01:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.