Marc, On Fri, Apr 29, 2016 at 1:50 PM, Marc L. Allen <mlallen at outsitenetworks.com> wrote: > 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.
Does anybody in MS "speak any English?" (C) ;-) Thank you. P.S.: This is a rhetorical question, BTW. > > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
