Lines Matching refs:a

4 # a legal notice, here is a blessing:
24   CREATE TABLE t1(a);
25   CREATE INDEX i1 ON t1(a);
29 do_execsql_test 1.1 { SELECT sum(a) FROM t1; } 45
30 do_execsql_test 1.2 { SELECT sum(a) FILTER( WHERE a<5 ) FROM t1; } 10
33   SELECT sum(a) FILTER( WHERE a>9 ),
34          sum(a) FILTER( WHERE a>8 ),
35          sum(a) FILTER( WHERE a>7 ),
36          sum(a) FILTER( WHERE a>6 ),
37          sum(a) FILTER( WHERE a>5 ),
38          sum(a) FILTER( WHERE a>4 ),
39          sum(a) FILTER( WHERE a>3 ),
40          sum(a) FILTER( WHERE a>2 ),
41          sum(a) FILTER( WHERE a>1 ),
42          sum(a) FILTER( WHERE a>0 )
47   SELECT max(a) FILTER (WHERE (a % 2)==0) FROM t1
51   SELECT min(a) FILTER (WHERE a>4) FROM t1
55   SELECT count(*) FILTER (WHERE a!=5) FROM t1
59   SELECT min(a) FILTER (WHERE a>3) FROM t1 GROUP BY (a%2) ORDER BY 1;
64   SELECT sum(a) FILTER( WHERE a>9 ),
65          sum(a) FILTER( WHERE a>8 ),
66          sum(a) FILTER( WHERE a>7 ),
67          sum(a) FILTER( WHERE a>6 ),
68          sum(a) FILTER( WHERE a>5 ),
69          sum(a) FILTER( WHERE a>4 ),
70          sum(a) FILTER( WHERE a>3 ),
71          sum(a) FILTER( WHERE a>2 ),
72          sum(a) FILTER( WHERE a>1 ),
73          sum(a) FILTER( WHERE a>0 )
82 #   .1 FILTER on a non-aggregate function,
88   CREATE TABLE t1(a);
93   SELECT upper(a) FILTER (WHERE a=1) FROM t1
97   SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
101   SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
107   CREATE TABLE t1(a,b);
111   SELECT b, max(a) FILTER (WHERE b='x') FROM t1;
115   CREATE TABLE t2(a, b, c);
122   SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;
134   SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;
140   CREATE TABLE t1(a, b, c);
141   INSERT INTO t1 VALUES('a', 0, 5);
142   INSERT INTO t1 VALUES('a', 1, 10);
143   INSERT INTO t1 VALUES('a', 0, 15);
155   SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY h;
158   SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY (h+1.0);
161   SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c);
162 } {c 2.0 a 10.0 b 5.0}
164   SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2
165 } {c 2.0 b 5.0 a 10.0}
170   CREATE TABLE t1(a, b);
190   CREATE TABLE t1(a,b);
198   SELECT (SELECT COUNT(a) FILTER(WHERE x) FROM t2) FROM t1;
201   SELECT (SELECT COUNT(a+x) FROM t2) FROM t1;
204   SELECT (SELECT COUNT(a) FROM t2) FROM t1;
210   CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
218   SELECT max(a), max(a) FILTER (WHERE b<12345), b FROM t1;