That error is saying that you can't using HAVING on a column unless it's in a group by or it's referenced in an aggregate in the HAVING clause
You could say, HAVING SUM(A) <> 0 or something. The query as stated SELECT SUM(A) ... HAVING A<>0 makes no sense because A is not in the select list. Only SUM(A) is. -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot Sent: Friday, April 29, 2016 1:42 PM To: SQLite mailing list <sqlite-users at mailinglists.sqlite.org> Subject: Re: [sqlite] Illegal SQL not rejected Hi, On Fri, Apr 29, 2016 at 1:28 PM, John McKown <john.archie.mckown at gmail.com> wrote: > On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder > <j.roder at wintoncapital.com> > wrote: > >> Hi, >> It seems like a too obvious omission to not be intentional. But I >> wonder why a query like >> >> SELECT SUM(A) >> FROM TABLE >> GROUP BY B >> HAVING A <> 0 >> >> Is not rejected. MS SQL server gives you this error message in this case: >> >> Column ?A? is invalid in the HAVING clause because it is not >> contained in either an aggregate function or the GROUP BY clause. >> > > I agree that it just looks _wrong_. And PostgreSQL certainly complains > about it. This is weird because A is part of sum(A), which IS aggregate function call. Or am I missing something? Thank you. > > > >> >> It's not even clear to me what SQLite does with a query like that. >> > > I executed the above both with the HAVING clause and without it. I > also did an EXPLAIN on both. Judging by the EXPLAIN output and the > actual output, what it seems to do is exclude rows which have SUM(A) > equal to zero. I.e. the HAVING is acting on the SUM(A). > > >> >> Jann >> > > > -- > The unfacts, did we have them, are too imprecisely few to warrant our > certitude. > > Maranatha! <>< > John McKown > _______________________________________________ > 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
