On Sun, 28 Dec 2014 17:46:08 +0100
Tomas Telensky <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
