Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 232899FB651 for ; Tue, 26 Jun 2007 16:40:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 65790-01 for ; Tue, 26 Jun 2007 16:39:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 665E49FB3AB for ; Tue, 26 Jun 2007 16:40:05 -0300 (ADT) Received: from root by ciao.gmane.org with local (Exim 4.43) id 1I3GtO-0005ri-Ap for pgsql-sql@postgresql.org; Tue, 26 Jun 2007 21:40:02 +0200 Received: from cpe.atm2-0-1261189.0x503fa7b2.bynxx8.customer.tele.dk ([80.63.167.178]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 26 Jun 2007 21:40:02 +0200 Received: from nis by cpe.atm2-0-1261189.0x503fa7b2.bynxx8.customer.tele.dk with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 26 Jun 2007 21:40:02 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: "news.gmane.org" Subject: Re: Where clause Date: Tue, 26 Jun 2007 17:22:26 +0200 Lines: 30 Message-ID: References: <20070626082405.GA67974@mich2.itxmarket.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: cpe.atm2-0-1261189.0x503fa7b2.bynxx8.customer.tele.dk User-Agent: Thunderbird 1.5.0.12 (Windows/20070509) In-Reply-To: <20070626082405.GA67974@mich2.itxmarket.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.729 tagged_above=0 required=5 tests=AWL=0.436, BAYES_40=-0.185, DATE_IN_PAST_03_06=0.478 X-Spam-Level: X-Archive-Number: 200706/227 X-Sequence-Number: 28717 Michael Landin Hostbaek skrev: > Hello, > > I have a table called tracking, with a contactid varchar, click bool, > view bool and cid varchar. > > I would like to put the following into one single query if possible: > > // Number of clicks > select cid,count(distinct contactid) from tracking where click = > true group by cid; > > // Number of views > select cid,count(distinct contactid) from tracking where view = > true group by cid; Untested, not the cleverest formulation, but something like this should work: SELECT * FROM ( select cid,count(distinct contactid) from tracking where click = true group by cid ) c1 FULL OUTER JOIN ( select cid,count(distinct contactid) from tracking where view = true group by cid ) c2 USING (cid);