Re: Count equals 0

Hi,<br /><br /> Thank you kindly for your suggetions.<br /> Greg's suggestion was closest to what I needed ( spot on
infact ) <br /><br /> Cheers<br /> Rudi.<br /><br /><a class="moz-txt-link-abbreviated"
href="mailto:greg@turnstep.com">greg@turnstep.com</a>wrote:<br /><blockquote
cite="midea0824a43c82b86591e6b913514cf5ba@biglumber.com"type="cite"><pre wrap="">-----BEGIN PGP SIGNED MESSAGE-----
 
Hash: SHA1

 </pre><blockquote type="cite"><pre wrap="">I've tries Left outer join's with and without coalesce without joy so far.
Please check out my query.   </pre></blockquote><pre wrap="">
Your query is good, let's just make the whole thing a subselect
and use an outer join to get the missing sites:

SELECT u2.site_name, COALESCE(u1.total, 0)
FROM
(  SELECT  site_name, COUNT(cus_id) AS total  FROM    user_main um  WHERE   NOT EXISTS          (          SELECT
cus_id         FROM    user_sys_messages usm          WHERE   usm.cus_id = um.cus_id          )  GROUP BY site_name
 
) AS u1
RIGHT OUTER JOIN user_main u2 USING (site_name)
GROUP BY 1,2
ORDER BY u2.site_name;


You could also write this as a LEFT OUTER JOIN (by putting the subselect second), 
but I think it is clearer if the USING or ON is as close as possible to the 
JOIN statement.


- --
Greg Sabino Mullane <a class="moz-txt-link-abbreviated" href="mailto:greg@turnstep.com">greg@turnstep.com</a>
PGP Key: 0x14964AC8 200303181009

-----BEGIN PGP SIGNATURE-----
Comment: <a class="moz-txt-link-freetext" href="http://www.turnstep.com/pgp.html">http://www.turnstep.com/pgp.html</a>

iD8DBQE+dzbwvJuQZxSWSsgRAj5FAJ4s2ZJgaBm8g4otdHHNI6VkhoElsgCg0Bkb
drxn19A1moxzQbAFy3Jv+Dc=
=tImk
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to <a class="moz-txt-link-abbreviated"
href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>
 </pre></blockquote><br /><br />