Обсуждение: COUNT and GROUP BY performing strangely
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
http://stackoverflow.com/questions/2054235/why-doesnt-postgres-group-by-null-select-counts
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
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-countssays "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
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