Do I just not understand count()
Author Message
Do I just not understand count()

Quote:
> 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

Quote:

> TIP 4: Don't 'kill -9' the postmaster

TIP 2: you can get off all lists at once with the unregister command

Sat, 25 Sep 2004 02:53:05 GMT
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

Quote:
----- Original Message -----

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

> > TIP 4: Don't 'kill -9' the postmaster

> TIP 2: you can get off all lists at once with the unregister command