Received: from mail1.waterford.org (mail1.waterford.org [205.124.117.6]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9IMjUP63516 for ; Thu, 18 Oct 2001 18:45:35 -0400 (EDT) (envelope-from olebedev@waterford.org) Received: from waterford.org ([205.124.138.93]) by mail1.waterford.org (Netscape Messaging Server 3.56) with ESMTP id 363; Thu, 18 Oct 2001 16:35:38 -0600 Message-ID: <3BCF5FF9.258EAC36@waterford.org> Date: Thu, 18 Oct 2001 17:04:25 -0600 From: Oleg Lebedev X-Mailer: Mozilla 4.72 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 To: Stephan Szabo , Postgres SQL Mailing List Subject: Re: COUNT func References: Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit X-Archive-Number: 200110/263 X-Sequence-Number: 5217 It worked! I checked the query plan it generates, and it's really a one-pass scan. thanks, Oleg Stephan Szabo wrote: > On Thu, 18 Oct 2001, Oleg Lebedev wrote: > > > Hi, > > I am trying to count the number or rows in a table with similar field > > values. I want to do it in one table scan. > > In other words, say I want to count the number of rows in Person table, > > having age equal to 40, and the number of rows with status as 'married'. > > I want Person table to be scanned only once during this query. > > So, basically I want to do the following: > > SELECT COUNT(age=40), COUNT(status='married') > > FROM Person; > > > > I tried to use aggregate functions with group by and having clauses, but > > I still can't figure it out (probably because of the lack of > > experience). > > Maybe something like [untested] > SELECT SUM(case when age=40 then 1 else 0 end), > SUM(case when status='married' then 1 else 0 end) > FROM PERSON; > will work?