Re: Do I just not understand count()

Try

select sum(case when a=1 then 1 else 0 end), sum(case when a=2 the 1 else 0
end) from t;

to get the number of occurrences of each value.

Jim Ballard

----- Original Message -----
From: "Clinton Adams" <clinton(at)vote-smart(dot)org>
To: "Ben" <bench(at)silentmedia(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Monday, April 08, 2002 12:49 PM
Subject: Re: [GENERAL] Do I just not understand count()

> > If I have the table t defined as:
> >
> > a
> > ---
> > 1
> > 1
> > 2
> >
> >
> > and I say:
> >
> > select count(a=1) from t;
> >
> > should it give me 1 or 2 as a result? I'm getting 2, and I'd think I
> > should get 1....
> It will give you a result of 3, as a=1 does not toss out rows for which a
<>
> 1.
>
> You would need to add some parameters to limit the rows that count is
> countin...
> select count(a) from t where a = 1
> would give you 2
>
> select count(DISTINCT a) from t where a = 1
> would give you 1
>
>
>
>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>