That is because count(a) and count(*) means two different things. The first counts values, the second counts rows.
-----Urspr?ngliche Nachricht----- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von TJ O'Donnell Gesendet: Freitag, 05. Februar 2016 05:34 An: Richard Hipp Cc: SQLite mailing list Betreff: Re: [sqlite] json_group_array I can't argue for the correctness of including nulls in aggregate functions or not. It truly is an arbitrary decision meant for standards-makers. Yet, most aggregate function do not include nulls. Interestingly, some SQL's do include them in count() but sqlite does not. In my example table, select count(a) from x returns 3, but select count(*) from x returns 4 even though a is the only column. I haven't tried every sqlite agg, but I think they all exclude null, except json_group_array and json_group_object. In my case, I'd sure like to NOT have nulls included, but I understand needing nulls in JSON. I'm having a hard time excluding null with a where clause, as you suggested, since my actual query is the results of an intentional left join that results in rows from one table that are null. If I do a normal join or exclude nulls with a where clause, I don't get all the rows from the other table that I require. As a side issue here, but important still I think, what should json(null) mean? In my table x, select json(a) from x returns valid json integers for non-null rows, but return a sql null (a blank from command-llne sqlite) not a json null (which would be the string null) when a is null. In other words, json(null) returns null, not 'null'. I know the json stuff is new in sqlite, but I think it's worth getting these issues worked out, considering how useful json has become. TJ O'Donnell On Thu, Feb 4, 2016 at 4:54 PM, Richard Hipp <drh at sqlite.org> wrote: > On 2/4/16, TJ O'Donnell <tjo at acm.org> wrote: > > I was expecting the json_group_array aggregate function to leave out > null, > > behaving like other aggregate functions. Is this to be expected? > > > > sqlite> create table x (a integer); > > sqlite> insert into x values (1), (2), (null),(4); select > > sqlite> group_concat(a), json_group_array(a) from x; > > group_concat(a) json_group_array(a) > > If I did that, then it would be impossible to generate a JSON array > using json_group_array() that contained NULL entries. As it is, you > can usually include or omit the NULL entries using a term in the WHERE > clause: > > select json_group_array(a) from x where x is not null; > > > -- > D. Richard Hipp > drh at sqlite.org > _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
