X-Original-To: pgsql-sql@postgresql.org
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 02181475461
	for <pgsql-sql@postgresql.org>; Tue, 18 Mar 2003 06:37:22 -0500 (EST)
Received: by serwer.skawsoft.com.pl (Postfix, from userid 1000)
	id 8F1F96A1DE; Tue, 18 Mar 2003 12:37:22 +0100 (CET)
Date: Tue, 18 Mar 2003 12:37:22 +0100
To: Rudi Starcevic <rudi@oasis.net.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Count equals 0
Message-ID: <20030318113722.GA16600@serwer.skawsoft.com.pl>
References: <3E76BCB9.9070603@oasis.net.au>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3E76BCB9.9070603@oasis.net.au>
User-Agent: Mutt/1.3.28i
From: jasiek@klaster.net
X-Archive-Number: 200303/431
X-Sequence-Number: 12470

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;