From: | Rudi Starcevic <rudi(at)oasis(dot)net(dot)au> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Count equals 0 |
Date: | 2003-03-20 05:38:48 |
Message-ID: | 3E7953E8.9090800@oasis.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
Thank you kindly for your suggetions.
Greg's suggestion was closest to what I needed ( spot on in fact )
Cheers
Rudi.
greg(at)turnstep(dot)com wrote:
>-----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-----
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tomasz Myrta | 2003-03-20 08:33:59 | Re: vacuum all but system tables |
Previous Message | Frankie Lam | 2003-03-20 04:35:26 | Is it possible to select encoding in PLPGSQL function? |