SQL count(*) giving wrong answer when used with group by

When I run this query the count(0) returns 21 for the set with zip='80005'.

select zip, avg(value), min(value), max(value), count(0) from values group by zip order by zip

There are really 109 rows with zip='80005'.

The following two queries both show 109 rows and they also return different values for min, max, and avg.

select avg(value), min(value), max(val开发者_运维问答ue), count(value) from values where zip='80005'

select zip, avg(value), min(value), max(value), count(value) from values group by zip having zip='80005'

There are no nulls for value.

Is there any reason why the first query is returning the wrong number of rows in the set for zip='80005'?

Maybe this is a bug in Postgresql.

I think you want count(*) not count(0)...

Count(1), Count(*) and Count([field]) all work.

There was one source I read that said there was a performance difference between Count(1) and Count(*) (allegedly, count(*) required more processing), however, there seems to be evidence at least for Postgress & TSQL that it doesn't make a difference.

At first glance, this looks like a bug, hard to say without seeing your data.

You could try and narrow down the source of the problem.

If this :-

  select zip, avg(value), min(value), max(value), count(value) 
  from values group by zip having zip='80005'

returns 109, but this :-

select zip, avg(value), min(value), max(value), count(0) 
from values group by zip order by zip

returns 21 for 80005, what is returned for 80005 when you do this :-

  select zip, avg(value), min(value), max(value), count(value) 
  from values group by zip

Look and see if you have records with zip 80005b where b is one or more trailing blanks. Those would collate somewhere else in your grouped zip list. I believe by default PG will ignore trailing blanks when testing two strings for equality, but not in a GROUP BY. (Do you have a sanity check constraint on the zip field?)