select3.test - OpenGrok cross reference for /illumos-gate/usr/src/lib/libsqlite/test/select3.test

Lines Matching refs:GROUP
14 # GROUP BY and HAVING clauses of SELECT statements.
53 # Try some basic GROUP BY clauses
56   execsql {SELECT log, count(*) FROM t1 GROUP BY log ORDER BY log}
59   execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log}
62   execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log}
65   execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log}
68   execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
71   execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
75     SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x
80     SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY y
85     SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y)
90     SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log;
92 } {1 {GROUP BY terms must not be non-integer constants}}
95     SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log;
97 } {1 {GROUP BY column number 0 out of range - should be between 1 and 2}}
100     SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log;
102 } {1 {GROUP BY column number 3 out of range - should be between 1 and 2}}
105     SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;
110 #    SELECT log, count(*) FROM t1 GROUP BY 2 ORDER BY log;
115 #    SELECT log, count(*) FROM t1 GROUP BY count(*) ORDER BY log;
119 # Cannot have a HAVING without a GROUP BY
124 } {1 {a GROUP BY clause is required before HAVING}}
129   execsql {SELECT log, count(*) FROM t1 GROUP BY log HAVING log>=4 ORDER BY log}
134     GROUP BY log
142     GROUP BY log
150     GROUP BY x
158     GROUP BY x
167     GROUP BY log
174     GROUP BY log
179 # Test sorting of GROUP BY results in the presence of an index
180 # on the GROUP BY column.
184     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;
189     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;
194     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;
199     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;
205     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;
210     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;
215     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;
220     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;