pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCount equals 0
5+ messages / 4 participants
[nested] [flat]
* Count equals 0
@ 2003-03-18 06:29 Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 3 replies; 5+ messages in thread
From: Rudi Starcevic @ 2003-03-18 06:29 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Count equals 0
@ 2003-03-18 11:37 jasiek@klaster.net
parent: Rudi Starcevic <rudi@oasis.net.au>
2 siblings, 0 replies; 5+ messages in thread
From: jasiek@klaster.net @ 2003-03-18 11:37 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; +Cc: pgsql-sql
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 um
left_join user_sys_messages usm using (cus_id)
group by um.site_name
order by um.site_name;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Count equals 0
@ 2003-03-18 15:31 greg@turnstep.com
parent: Rudi Starcevic <rudi@oasis.net.au>
2 siblings, 1 reply; 5+ messages in thread
From: greg@turnstep.com @ 2003-03-18 15:31 UTC (permalink / raw)
To: 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@turnstep.com
PGP Key: 0x14964AC8 200303181009
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+dzbwvJuQZxSWSsgRAj5FAJ4s2ZJgaBm8g4otdHHNI6VkhoElsgCg0Bkb
drxn19A1moxzQbAFy3Jv+Dc=
=tImk
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Count equals 0
@ 2003-03-18 16:35 Josh Berkus <josh@agliodbs.com>
parent: Rudi Starcevic <rudi@oasis.net.au>
2 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2003-03-18 16:35 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Count equals 0
@ 2003-03-20 05:38 Rudi Starcevic <rudi@oasis.net.au>
parent: greg@turnstep.com
0 siblings, 0 replies; 5+ messages in thread
From: Rudi Starcevic @ 2003-03-20 05:38 UTC (permalink / raw)
To: 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@turnstep.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@turnstep.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@postgresql.org
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-03-20 05:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-18 06:29 Count equals 0 Rudi Starcevic <rudi@oasis.net.au>
2003-03-18 11:37 ` jasiek@klaster.net
2003-03-18 15:31 ` greg@turnstep.com
2003-03-20 05:38 ` Rudi Starcevic <rudi@oasis.net.au>
2003-03-18 16:35 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox