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

That's what I'd normally do to, but in this case I want to run a query
more like

select count(a=1), count(a=2) from t

and I don't want to do multiple selects, because I'm selecting other stuff
too, which takes time, and I figure as long as postgres is looking at
those rows, it might as well tally up the counts of a=1 and a=2.

Quote:

> I don't think I've seen that particular syntax used before (I would say
> select count(a) from t where a=1;), but since the query appears to work, I
> won't argue.

> Why do you think it should give you a result of 1? There are two rows
> containing a value of 1 for a, hence it returns 2.

> Greg

> ----- Original Message -----


> Sent: Monday, April 08, 2002 2:25 PM
> Subject: [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....

> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.***.com/



Sat, 25 Sep 2004 02:46:27 GMT
 Do I just not understand count()?

I'm fairly sure you can't return a conditional count... count likes to
merely return the number of rows that match the query, which in this case is
all of them. I'm not sure exactly what it does with "a=1" though... I'm
guessing that it returns a boolean value, which means that it counts as a
row (only NULL values are not counted in count()).

If you're selecting other stuff, you won't be able to return more than one
row anyway... you'll have to either use a subquery or do a GROUP BY.

Greg

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



Sent: Monday, April 08, 2002 2:45 PM
Subject: Re: [GENERAL] Do I just not understand count()?

> That's what I'd normally do to, but in this case I want to run a query
> more like

> select count(a=1), count(a=2) from t

> and I don't want to do multiple selects, because I'm selecting other stuff
> too, which takes time, and I figure as long as postgres is looking at
> those rows, it might as well tally up the counts of a=1 and a=2.


> > I don't think I've seen that particular syntax used before (I would say
> > select count(a) from t where a=1;), but since the query appears to work,
I
> > won't argue.

> > Why do you think it should give you a result of 1? There are two rows
> > containing a value of 1 for a, hence it returns 2.

> > Greg

> > ----- Original Message -----


> > Sent: Monday, April 08, 2002 2:25 PM
> > Subject: [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....

> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Sat, 25 Sep 2004 03:03:12 GMT
 Do I just not understand count()?

Quote:

> I'm not sure exactly what it does with "a=1" though... I'm
> guessing that it returns a boolean value, which means that it counts as a
> row (only NULL values are not counted in count()).

Yup, Greg gets a gold star: COUNT *only* cares whether its input is NULL
or not, not what specific value it might have.  This is per SQL spec.

The nearby suggestions involving SUM() look like they would work to
accumulate counts of different conditions in a single pass.

                        regards, tom lane

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



Sat, 25 Sep 2004 03:32:54 GMT
 Do I just not understand count()?
This is the first time I've tried. I noticed that I could add a DISTINCT
clause to count(), and wondered why I couldn't add an equality and make it
work as well.

Quote:

> Perhaps it would clear up things for those more expert on SQL [and it's
> variations across DBs] than I if you could say on what system you would
> normally do this.

> > > That's what I'd normally do to, but in this case I want to run a query
> > > more like

> > > select count(a=1), count(a=2) from t

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

> --
> Nigel J. Andrews
> Director

> ---
> Logictree Systems Limited
> Computer Consultants

> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?

> http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly


Sat, 25 Sep 2004 04:30:40 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Do I just not understand count()

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

3. About doing a count

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

5. Do not understand

6. Syntax error not understood

7. Not understand the #ifdef _DEBUG

8. Counld Not Understand Estimated Execution Plan

9. Strange error creating index - not sure I understand what's gone wrong

10. Maybe I did not quite understand you

11. Do not understand SQL Stmt.

12. JDBC timestamp does not understand [-]infinity


 
Powered by phpBB® Forum Software