This is completely legal and well defined. HAVING is applied to the RESULT set of a SELECT.
The select asks to count the "distinct kontrola" in each group of kvadrat and datum, the HAVING clause specifies returning only those records with pocet > 1. If there were no pocet column in table b, this would return only the non-empty groups, which is what the OP intended. As there is a pocet column in table b, the HAVING clause refers to the original b.pocet which contains a (from the POV of the programmer) "randomly selected from the group" rows' value. This is a documented SQLite feature. SELECT kvadrat, datum, counted_pocet as pocet from (SELECT kvadrat, datum ,pocet, count(distinct kontrola) as counted_pocet from b group by kvadrat, datum HAVING pocet > 1); -----Ursprüngliche Nachricht----- Von: James K. Lowden [mailto:jklow...@schemamania.org] Gesendet: Samstag, 03. Jänner 2015 00:45 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn On Sun, 28 Dec 2014 17:46:08 +0100 Tomas Telensky <tomas.telen...@gmail.com> wrote: > select kvadrat, datum, count(distinct kontrola) as pocet from b group > by kvadrat, datum having pocet > 1 > > The problem was that pocet was actually a column in table b and I > didn't notice, and the having clause was using the table column > instead of the newly derived column specified in select clause. > > So far so good, but sqlite should at least issue any warning, right? I would say it should raise an error. The HAVING clause should include at least one aggregate. Comparing a column to a constant is the job of WHERE. The accepted syntax is ambiguous. Was the HAVING applied before or after the aggregation. IOW, did you get 1. the count for each {kvadrat, datum} pair for which pocet > 1, or 2. the count of {kvadrat, datum} pairs that have at least one pocet > 1 ? In the first case the counts would be smaller by the number of rows for which pocet <= 1. In the second case results rows would be eliminated for pairs that contain only rows for which pocet <= 1. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
