Thread: COUNT func
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). Please help. Thanks, Oleg
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?
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?
hi, try this too.. select count(*),age,status from person where age=40 and status='MARRIED' group by age,status; Regards, Bhuvaneswar. On Oct 18, 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). > Please help. > Thanks, > > Oleg > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >