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:

> ---------------------------(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



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

> > ---------------------------(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

---------------------------(end of broadcast)---------------------------




Sat, 25 Sep 2004 03:11:13 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Do I just not understand count()?

2. Not happy together: COUNT(*) and COUNT(DISTINCT ...)

3. determine num rows in a table without doing a count(*)

4. About doing a count

5. Do not understand

6. Maybe I did not quite understand you

7. JDBC timestamp does not understand [-]infinity

8. Do not understand SQL Stmt.

9. Don't understand Fullpath() - not getting results I expect

10. I don't understand why this worked in 7.0 and now not in 2000

11. I don't understand why this view is not updatable

12. Terms not understanding while reading BOL


 
Powered by phpBB® Forum Software