I just love this list and the contained discussions. Doffing my cap to Keith and his succinct and very useful summary of the subtle differences regarding aggregate type functions. Especially like the nested stuff, which I must admit had not really occurred to me. Nice stuff Keith and thanks for sharing that info. It also clarifies the underlying fact that these functions are often slightly misunderstood even by experienced SQL hacks.
On 6 February 2016 at 07:24, Keith Medcalf <kmedcalf at dessus.com> wrote: > > count(*) counts the rows of the result set selected > count(column) counts the NOT NULL values in the column of the result set > selected > count(DISTINCT column) counts the number of distinct values (excluding > NULLs) in the column of the result set selected > > count(column IS NULL) is equivalent to count(*) (the expression always > returns true (1) or false(0) > count(column IS NOT NULL) is equivalent to count(*) for every row so > therefore all rows are counted) > > sum(column IS NULL) returns the count of the number of rows in which the > column is null > sum(column IS NOT NULL) returns the count of the number of rows in which > the column is not null > > These can be combined, so for example count(DISTINCT column) + sum(column > IS NULL) returns the number of unique values in the column where NULLs are > distinct. > and count(DISTINCT column) + (sum(column IS NULL) > 0) returns the number > of unique values where NULLs are not distinct. > > and so on and so forth > > > -----Original Message----- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > > bounces at mailinglists.sqlite.org] On Behalf Of R Smith > > Sent: Friday, 5 February, 2016 00:55 > > To: sqlite-users at mailinglists.sqlite.org > > Subject: Re: [sqlite] json_group_array > > > > > > > > On 2016/02/05 6:34 AM, TJ O'Donnell wrote: > > > 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. > > > > I think you are mistaken in your understanding. While JSON has some > > rules and some conventions, when used inside an SQL engine, the rules of > > SQL needs to be adhered to before any "convention" of JSON. > > Not showing Null values in JSON is a convention, not a rule. (Else, why > > else would json even need the 'NULL' construct?) > > > > Further to this, in SQL, how would you know how many elements are > > present in a json array and which of them are null if there is no way to > > output them? Agreed, sometimes it isn't needed to know, but then you are > > welcome to exclude them via the WHERE clause. > > > > You are also mistaken about the SQL convention and SQLite-specific > > operations re. Nulls - If I have a table t with one single column "a" > > with 3 rows (2 text values and one null value) then doing SELECT > > COUNT(a) FROM t; will show 2 and SELECT COUNT(*) FROM t; will show 3, as > > it should - yes, even though a is the only column. The * doesn't mean > > "a", even if the only column is "a". It means "all the DB rows" and so > > include nulls. (The standard might be hazy on this, I didn't check, but > > this is definitely how SQLite works, and not as you suggested). > > > > This is also very important. Sometimes we'd want to know how many rows > > are in the DB, not JUST which non-null rows are in the only column in > > the DB - that is why we can decide to use either COUNT(a) or COUNT(*), > > or more deliberate with an explicit GROUP BY clause. I would never want > > this convention to be altered. > > > > > > > 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'. > > > > Here I'm with you - the null should output 'null' > > (Devs: I'm guessing this might be an oversight in the CLI rather than > > the SQL engine?) > > > > > 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. > > > > Right you are, but first the issues need discovery - which is what is > > happening in this very thread. :) > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Michael.j.Falconer.
