From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Doug Younger <postgres(at)mindspring(dot)com> |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] Problems using count() with a join |
Date: | 1999-08-19 13:16:30 |
Message-ID: | 7784.935068590@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Doug Younger <postgres(at)mindspring(dot)com> writes:
> What I want is to get a count of # of users in each group, even if there
> are no users in the group.
> I have tried the following:
> SELECT t1.g_name,count(t2.g_id)
> FROM groups t1,users t2
> WHERE t1.g_id = t2.g_id
> GROUP BY t1.g_name;
> But it only give the counts of users in each group if there actually are
> users in that group.
> I want the count to be 0 if there are no users in the group.
Try SELECT as above plus
... UNION
SELECT t1.g_name, 0
FROM groups t1
WHERE NOT EXISTS(SELECT * FROM users t2 WHERE t1.g_id = t2.g_id);
Grotty, I know, but we don't have outer joins yet...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Dirk Lutzebaeck | 1999-08-19 13:17:46 | does LIMIT save time? |
Previous Message | Jon Bundesen | 1999-08-19 11:23:33 | What JDBC datatype can be used for DATETIME ? |