select count(distinct ID)...

[gerardg]

I don't think I fully understand how distinct works. Here's a query:

select a.ID, count(distinct b.ID)
from a
left outer join b
on a.ID = b.ID
group by a.ID

a to b is one-to-many. If there aren't any rows in b, count returns '1' for each row, but if there are any rows in b then (what I expect to be) normal counts will be returned (0's if no b rows for that ID). Best I can figure is NULL is being counted, but why only if there are no rows in b?

[[deleted]]

"If there aren't any rows in b, count returns '1' for each row,"

on which database? I can't reproduce that on postgresql nor on mysql.

"Best I can figure is NULL is being counted, but why only if there are no rows in b?"

That's the whole point of doing an OUTER JOIN. All other JOINs will return no records fro the LEFT table if there is no mathc in the RIGHT table.
Only the OUTER JOIN will return records regardless of wether there is a match or not, and if there is no match, the 'missing' record is filled with NULL values.

---

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[gerardg]

It's with mysql and it does it for me on both win32 and linux. Here's the query:

select s.*, count(distinct su.User_ID) as User_Count
from sites s
left outer join site_users su
on s.ID = su.Site_ID
group by s.ID

It works as I want when there's at least one row in site_users, but if there aren't any rows in site_users User_Count is '1' for all rows.

sites has name, url, etc. site_users has Site_ID and User_ID. There may be duplicate rows in site_users. I want the number of distinct users for each site.

[gerardg]

lol, just wondering if you figured this one out ;-)

[gerardg]

ha - turns out it was a bug that was fixed in 3.23.40

[[deleted]]

Well darn it, I completely missed that one.. lol sorry dude...