I replicated your results. ORDERing the results seems to resolve the error but clearly the GROUP BY is otherwise getting confused by the CASE.
This works: select categ, count(1) from (select *, (case when a=0 then 0 else cast((a + 25) / 50 + 0.5 as int) end) as categ from test order by a) a group by categ; -- right or this: select categ, count(1) from (select *, (case when a=0 then 0 else cast((a + 25) / 50 + 0.5 as int) end) as categ from test order by categ) a group by categ; -- right and this, w/o either the CASE or the ORDER, except it does not handle the a=0 condition: select cast((a + 25) / 50 + 0.5 as int) as categ, COUNT(1) from test group by categ; -- almost right There seem to be a couple of unused elements in your query and it could be written: select categ, count(1) from (select (case when a=0 then 0 else cast((a + 25) / 50 + 0.5 as int) end) as categ from test order by a) group by categ; -- right Tom "Pedro Pedruzzi" <pedro.pedru...@gmail.com> wrote in message news:4b901b1d.8090...@gmail.com... > I'm using sqlite3-3.6.22, downloaded today from sqlite web site. > > Steps to reproduce: > > create table test(a real); > .import bugdata test > > select categ, count(1) from (select *, (case when a=0 then 0 else > cast((a + 25) / 50 + 0.5 as int) end) as categ from test) a group by > categ; > > 1|10 > 1|25 > 3|26 > > The count is ok, but the categ is not supposed to duplicate. > > This next very similar query works ok (with the provided data in > particular the correct results happens to be the same). > > select categ, count(1) from (select *, (case when 0=1 then 0 else > cast((a + 25) / 50 + 0.5 as int) end) as categ from test) a group by > categ; > > 1|10 > 2|25 > 3|26 > > > Here is the bugdata: > > 120.0 > 35.5 > 95.0 > 41.0 > 51.5 > 64.5 > 140.0 > 64.5 > 108.5 > 138.5 > 138.5 > 94.5 > 130.5 > 119.5 > 148.5 > 75.5 > 94.0 > 144.0 > 78.5 > 86.0 > 112.0 > 132.0 > 51.0 > 42.0 > 44.0 > 48.0 > 12.0 > 35.5 > 35.5 > 75.5 > 77.5 > 130.5 > 103.0 > 110.5 > 53.5 > 86.5 > 122.0 > 146.0 > 129.0 > 91.5 > 141.0 > 76.5 > 66.5 > 35.5 > 126.0 > 90.0 > 96.0 > 134.0 > 63.0 > 106.5 > 77.5 > 35.5 > 64.0 > 121.0 > 119.5 > 126.0 > 58.0 > 123.0 > 133.0 > 77.0 > 56.0 > > Regards, > -- Pedro Pedruzzi > _______________________________________________ > 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
