Обсуждение: Count equals 0
Greetings, I have what I think is an easy query but is not working out for me. So I thought I'd ask .. I have 2 tables : user_main user_sys_messages In user_main I have many user's which belong to 3 web sites. In my admin tool I can send a user an email message and that message is logged to the user_sys_messages table. All that is good. What I want to report is those who have *not* recieved any email from me. If each of the 3 web site's has a user in user_main who has not recieved any messages the report form the below query looks like site a : 1 site b : 3 site c : 6 My problem is if a site has no one that has not recieved any message they do not appear in the report. Ie. site b : 3 site c : 6 What I would like to see is something like: site a : 0 site b : 3 site c : 6 I've tries Left outer join's with and without coalesce without joy so far. Please check out my query. Thank you kindly. -- new members --- members who haven't recieved any email from us SELECT um.site_name, count(um.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 um.site_name ORDER BY um.site_name Regards Rudi.
On Tue, Mar 18, 2003 at 04:29:13PM +1000, Rudi Starcevic wrote: <cut> > What I would like to see is something like: > > site a : 0 > site b : 3 > site c : 6 <cut> Maybe something like this: select um.site_name,count(*)-sum(case when usm.cus_id is null then 1 else 0 end) as total from user_main umleft_join user_sys_messages usm using (cus_id)group by um.site_nameorder by um.site_name;
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I've tries Left outer join's with and without coalesce without joy so far. > Please check out my query. 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 greg@turnstep.com PGP Key: 0x14964AC8 200303181009 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+dzbwvJuQZxSWSsgRAj5FAJ4s2ZJgaBm8g4otdHHNI6VkhoElsgCg0Bkb drxn19A1moxzQbAFy3Jv+Dc= =tImk -----END PGP SIGNATURE-----
Rudi, > What I would like to see is something like: > > site a : 0 > site b : 3 > site c : 6 > > I've tries Left outer join's with and without coalesce without joy so far. > Please check out my query. You're going to need to nest your query: SELECT um2.site_name, COALESCE(count_um.total, 0) as total FROM user_main um2 LEFT OUTER JOIN (SELECT um.site_name, count(um.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 um.site_name) count_um ON um2.site_name = count_um.site_name ORDER BY um2.site_name -- Josh Berkus Aglio Database Solutions San Francisco
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 />