From: | greg(at)turnstep(dot)com |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Count equals 0 |
Date: | 2003-03-18 15:31:47 |
Message-ID: | ea0824a43c82b86591e6b913514cf5ba@biglumber.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200303181009
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+dzbwvJuQZxSWSsgRAj5FAJ4s2ZJgaBm8g4otdHHNI6VkhoElsgCg0Bkb
drxn19A1moxzQbAFy3Jv+Dc=
=tImk
-----END PGP SIGNATURE-----
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Haller | 2003-03-18 15:44:03 | Re: Trigger issue, bug? on 7.2.1 |
Previous Message | Alan Roberto Romaniuc | 2003-03-18 15:24:03 | Numeric type |