Thread: COUNT and GROUP BY performing strangely

COUNT and GROUP BY performing strangely

From

James David Smith

Date:

25 October 2013, 13:09:09

Hi everyone,

Any thoughts on the below three queries please?

james_traffic=# SELECT  count(*) FROM stage;
 count
--------
 430991
(1 row)

james_traffic=# SELECT bad_flag, count(bad_flag) FROM stage GROUP BY bad_flag;
 bad_flag | count
----------+-------
           |     0
        1 |  4102
(2 rows)

james_traffic=# SELECT count(bad_flag) FROM stage;
 count
-------
  4102
(1 row)

 I'm confused as to why the second query doesn't return this:

 bad_flag  | count
--------------+-------
               | 426889
        1     |  4102

It should count the records surely, even if the field is blank/null ?

Thanks

James

Re: COUNT and GROUP BY performing strangely

From

Jayadevan M

Date:

25 October 2013, 14:22:29

http://stackoverflow.com/questions/2054235/why-doesnt-postgres-group-by-null-select-counts

http://www.postgresql.org/docs/9.0/static/functions-aggregate.html

says "number of input rows for which the value ofexpression is not null"

On Fri, Oct 25, 2013 at 3:38 PM, James David Smith <james.david.smith@gmail.com> wrote:

Hi everyone,

Any thoughts on the below three queries please?

james_traffic=# SELECT count(*) FROM stage;
count
--------
430991
(1 row)

james_traffic=# SELECT bad_flag, count(bad_flag) FROM stage GROUP BY bad_flag;
bad_flag | count
----------+-------
| 0
1 | 4102
(2 rows)

james_traffic=# SELECT count(bad_flag) FROM stage;
count
-------
4102
(1 row)

I'm confused as to why the second query doesn't return this:

bad_flag | count
--------------+-------
| 426889
1 | 4102

It should count the records surely, even if the field is blank/null ?

Thanks

James

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: COUNT and GROUP BY performing strangely

From

James David Smith

Date:

25 October 2013, 14:24:48

Thanks. I never realized it behaved like that. Will correct queries.

On 25 Oct 2013 12:22, "Jayadevan M" <maymala.jayadevan@gmail.com> wrote:

http://stackoverflow.com/questions/2054235/why-doesnt-postgres-group-by-null-select-counts
http://www.postgresql.org/docs/9.0/static/functions-aggregate.html
says "number of input rows for which the value ofexpression is not null"

On Fri, Oct 25, 2013 at 3:38 PM, James David Smith <james.david.smith@gmail.com> wrote:
Hi everyone,

Any thoughts on the below three queries please?

james_traffic=# SELECT count(*) FROM stage;
count
--------
430991
(1 row)

james_traffic=# SELECT bad_flag, count(bad_flag) FROM stage GROUP BY bad_flag;
bad_flag | count
----------+-------
| 0
1 | 4102
(2 rows)

james_traffic=# SELECT count(bad_flag) FROM stage;
count
-------
4102
(1 row)

I'm confused as to why the second query doesn't return this:

bad_flag | count
--------------+-------
| 426889
1 | 4102

It should count the records surely, even if the field is blank/null ?

Thanks

James

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: COUNT and GROUP BY performing strangely

From

Thomas Kellerer

Date:

25 October 2013, 18:19:15

James David Smith, 25.10.2013 12:08:

> james_traffic=# SELECT  count(*) FROM stage;
>  count
> --------
>  430991
> (1 row)
>
> james_traffic=# SELECT bad_flag, count(bad_flag) FROM stage GROUP BY bad_flag;
>  bad_flag | count
> ----------+-------
>            |     0
>         1 |  4102
> (2 rows)
>
> james_traffic=# SELECT count(bad_flag) FROM stage;
>  count
> -------
>   4102
> (1 row)
>
>  I'm confused as to why the second query doesn't return this:
>
>  bad_flag  | count
> --------------+-------
>                | 426889
>         1     |  4102
>
> It should count the records surely, even if the field is blank/null ?
>

This is because count(bad_flag) is equivalent to count(*) where bad_flag is not null