Thread

1. Re: Do I just not understand count()

   Clinton Adams <clinton@vote-smart.org> — 2002-04-08T16:49:20Z

   > 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
   
   

2. Do I just not understand count()?

   Ben Chobot <bench@silentmedia.com> — 2002-04-08T18:25:43Z

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

3. Re: Do I just not understand count()?

   Gregory Wood <gregw@com-stock.com> — 2002-04-08T18:42:01Z

   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 -----
   From: "Ben" <bench@silentmedia.com>
   To: <pgsql-general@postgresql.org>
   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
   >
   
   
   

4. Re: Do I just not understand count()?

   Ben Chobot <bench@silentmedia.com> — 2002-04-08T18:45:28Z

   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.
   
   On Mon, 8 Apr 2002, Gregory Wood wrote:
   
   > 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 -----
   > From: "Ben" <bench@silentmedia.com>
   > To: <pgsql-general@postgresql.org>
   > 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
   > >
   >
   
   
   

5. Re: Do I just not understand count()?

   Gregory Wood <gregw@com-stock.com> — 2002-04-08T19:03:27Z

   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
   
   ----- Original Message -----
   From: "Ben" <bench@silentmedia.com>
   To: "Gregory Wood" <gregw@com-stock.com>
   Cc: "PostgreSQL-General" <pgsql-general@postgresql.org>
   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.
   >
   > On Mon, 8 Apr 2002, Gregory Wood wrote:
   >
   > > 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 -----
   > > From: "Ben" <bench@silentmedia.com>
   > > To: <pgsql-general@postgresql.org>
   > > 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
   > > >
   > >
   >
   >
   
   
   

6. Re: Do I just not understand count()

   Jim Ballard <jballard@netezza.com> — 2002-04-08T19:04:30Z

   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@vote-smart.org>
   To: "Ben" <bench@silentmedia.com>
   Cc: <pgsql-general@postgresql.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@postgresql.org)
   >
   
   
   

7. Re: Do I just not understand count()?

   Nigel J. Andrews <nandrews@investsystems.co.uk> — 2002-04-08T19:05:27Z

   
   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
   
   
   
   

8. Re: Do I just not understand count()?

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-08T19:30:41Z

   "Gregory Wood" <gregw@com-stock.com> writes:
   > 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
   
   

9. Re: Do I just not understand count()?

   Ben Chobot <bench@silentmedia.com> — 2002-04-08T20:04:54Z

   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.
   
   On Mon, 8 Apr 2002, Nigel J. Andrews wrote:
   
   >
   >
   > 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
   >