pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
COUNT func
4+ messages / 3 participants
[nested] [flat]

* COUNT func
@ 2001-10-18 19:37 Oleg Lebedev <olebedev@waterford.org>
  2001-10-18 22:39 ` Re: COUNT func Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-10-19 06:20 ` Re: COUNT func Bhuvan A <bhuvansql@yahoo.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Oleg Lebedev @ 2001-10-18 19:37 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: COUNT func
  2001-10-18 19:37 COUNT func Oleg Lebedev <olebedev@waterford.org>
@ 2001-10-18 22:39 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-10-18 23:04   ` Re: COUNT func Oleg Lebedev <olebedev@waterford.org>
  1 sibling, 1 reply; 4+ messages in thread

From: Stephan Szabo @ 2001-10-18 22:39 UTC (permalink / raw)
  To: Oleg Lebedev <olebedev@waterford.org>; +Cc: pgsql-sql


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?




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: COUNT func
  2001-10-18 19:37 COUNT func Oleg Lebedev <olebedev@waterford.org>
  2001-10-18 22:39 ` Re: COUNT func Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-10-18 23:04   ` Oleg Lebedev <olebedev@waterford.org>
  0 siblings, 0 replies; 4+ messages in thread

From: Oleg Lebedev @ 2001-10-18 23:04 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-sql

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?




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: COUNT func
  2001-10-18 19:37 COUNT func Oleg Lebedev <olebedev@waterford.org>
@ 2001-10-19 06:20 ` Bhuvan A <bhuvansql@yahoo.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Bhuvan A @ 2001-10-19 06:20 UTC (permalink / raw)
  To: Oleg Lebedev <olebedev@waterford.org>; +Cc: pgsql-sql



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
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-10-19 06:20 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-18 19:37 COUNT func Oleg Lebedev <olebedev@waterford.org>
2001-10-18 22:39 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-18 23:04   ` Oleg Lebedev <olebedev@waterford.org>
2001-10-19 06:20 ` Bhuvan A <bhuvansql@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox