aggbuiltin.sql

derby database source code.good for you.

select count(i) from t group by i;
select count(s) from t group by s;
select count(l) from t group by l;
select count(c) from t group by c;
select count(v) from t group by v;
select count(d) from t group by d;
select count(r) from t group by r;
select count(dt) from t group by dt;
select count(t) from t group by t;
select count(ts) from t group by ts;
select count(b) from t group by b;
select count(bv) from t group by bv;
select count(lbv) from t group by lbv;
select count(dc) from t group by dc;


-- constants
select count(1) from t;
select count('hello') from t;
select count(1.1) from t;
select count(1e1) from t;
select count(X'11') from t;
select count(date('1999-06-06')) from t;
select count(time('12:30:30')) from t;
select count(timestamp('1999-06-06 12:30:30')) from t;

select count(1) from t group by i;
select count('hello') from t group by c;
select count(1.1) from t group by dc;
select count(1e1) from t group by r;
select count(X'11') from t group by b;
select count(date('1999-06-06')) from t group by dt;
select count(time('12:30:30')) from t group by t;
select count(timestamp('1999-06-06 12:30:30')) from t group by ts;


-- multicolumn grouping
select count(i), count(dt), count(b) from t group by i, dt, b;
select l, dt, count(i), count(dt), count(b), i from t group by i, dt, b, l; 

-- group by expression
select count(expr1), count(expr2)
from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1;


-- distinct and group by
select distinct count(i) from t group by i, dt;


-- insert select
create table tmp (x int, y smallint);
insert into tmp (x, y) select count(i), count(c) from t;
select * from tmp;
insert into tmp (x, y) select count(i), count(c) from t group by b;
select * from tmp;
drop table tmp;

-- drop tables
drop table t;
drop table empty;
-- ** insert countStar.sql
-- Test the COUNT() aggregate

-- create an all types tables
create table t (i int, s smallint, l bigint,
				c char(10), v varchar(50), lvc long varchar,
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar for bit data);

-- empty table
create table empty (i int, s smallint, l bigint,
				c char(10), v varchar(50), lvc long varchar,
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar for bit data);

-- populate tables
insert into t (i) values (null);
insert into t (i) values (null);

insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (1, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 200, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 2000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'goodbye', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'duplicate', 'noone is here', 'jimmie noone was here',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  100.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 100.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
					  X'12af', X'0000111100001111', X'ABCD');
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'ffff', X'0000111100001111', X'1234');
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 'also duplicated',
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'1111111111111111', X'ABCD');

---------------------------
-- NULL AGGREGATION
---------------------------

-- scalar
select count(*) from empty;

-- variations
select count(*), count(*) from empty;

-- vector
select count(*) from empty group by i;

--------------------------------
-- BASIC ACCEPTANCE TESTS
--------------------------------
select count(*) from t;

select count(*) from t group by i;


-- multicolumn grouping
select count(*), count(*), count(*) from t group by i, dt, b;

-- group by expression
select count(*), count(*)
from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1;

-- distinct and group by
select distinct count(*) from t group by i, dt;

-- view
create view v1 as select * from t;
select count(*) from v1;
select count(*)+count(*) from v1;
drop view v1;

-- insert select 
create table tmp (x int, y smallint);
insert into tmp (x, y) select count(*), count(*) from t;
select * from tmp;
insert into tmp (x, y) select count(*), count(*) from t group by b;
select * from tmp;
drop table tmp;

-- drop tables
drop table t;
drop table empty;
-- ** insert sum.sql
--BUGS: sum() on decimal may overflow the decimal,
--w/o the type system knowing.  so, given dec(1,0),
--result might be dec(2,0), but return length passed
--to connectivity is 1 which is wrong.  if we allow
--the decimal to grow beyond the preset type, we need
--to all the type system to get it.  alternatively, 
--need to cast/normalize/setWidth() the result to ensure 
--right type.

-- create an all types tables
create table t (i int, s smallint, l bigint,
				c char(10), v varchar(50), 
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2));

-- empty table
create table empty (i int, s smallint, l bigint,
				c char(10), v varchar(50), 
				d double precision, r real, 
				dt date, t time, ts timestamp,
				b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2));
-- bit maps to Byte[], so can't test for now

-- populate tables
insert into t (i) values (null);
insert into t (i) values (null);

insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (1, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 200, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 222.22);
insert into t values (0, 100, 2000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 222.22);
insert into t values (0, 100, 1000000,
					  'goodbye', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'noone is here', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  100.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 100.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
					  X'12af', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'ffff', X'0000111100001111', 111.11);
insert into t values (0, 100, 1000000,
					  'duplicate', 'this is duplicated', 
					  200.0e0, 200.0e0, 
					  date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
					  X'12af', X'1111111111111111', 111.11);
-- bit maps to Byte[], so can't test for now

--------------------------------------
-- NEGATIVE TESTS
--------------------------------------

-- cannot aggregate datatypes that don't support NumberDataValue
select sum(c) from t;
select sum(v) from t;
select sum(dt) from t;
select sum(t) from t;
select sum(ts) from t;
select sum(b) from t;
select sum(bv) from t;

select sum(c) from t group by c;
select sum(v) from t group by c;
select sum(dt) from t group by c;
select sum(t) from t group by c;
select sum(ts) from t group by c;
select sum(b) from t group by c;
select sum(bv) from t group by c;

-- long varchar datatypes too
create table t1 (c1 long varchar);
select sum(c1) from t1;
drop table t1;

-- constants
select sum('hello') from t;
select sum(X'11') from t;
select sum(date('1999-06-06')) from t;
select sum(time('12:30:30')) from t;
select sum(timestamp('1999-06-06 12:30:30')) from t;

---------------------------
-- NULL AGGREGATION
---------------------------

-- scalar
select sum(i) from empty;
select sum(s) from empty;
select sum(d) from empty;
select sum(l) from empty;
select sum(r) from empty;
select sum(dc) from empty;

-- variations
select sum(i), sum(s), sum(r), sum(l) from empty;
select sum(i+1) from empty;

-- vector
select sum(i) from empty group by i;
select sum(s) from empty group by s;
select sum(d) from empty group by d;
select sum(l) from empty group by l;
select sum(r) from empty group by r;
select sum(dc) from empty group by dc;


--------------------------------
-- BASIC ACCEPTANCE TESTS
--------------------------------
select sum(i) from t;
select sum(s) from t;
select sum(d) from t;
select sum(l) from t;
select sum(r) from t;
select sum(dc) from t;

select sum(i) from t group by i;
select sum(s) from t group by s;
select sum(d) from t group by d;
select sum(l) from t group by l;
select sum(r) from t group by r;
select sum(dc) from t group by dc;

-- constants
select sum(1) from t;
select sum(1.1) from t;
select sum(1e1) from t;

select sum(1) from t group by i;
select sum(1.1) from t group by r;
select sum(1e1) from t group by r;

-- multicolumn grouping
select sum(i), sum(l), sum(r) from t group by i, dt, b;
select i, dt, sum(i), sum(r), sum(l), l from t group by i, dt, b, l; 

-- group by expression
select sum(expr1), sum(expr2)
from (select i * s, r * 2 from t) t (expr1, expr2) group by expr2, expr1;

-- distinct and group by
select distinct sum(i) from t group by i, dt;

-- insert select
create table tmp (x int, y smallint);
insert into tmp (x, y) select sum(i), sum(s) from t;
select * from tmp;
insert into tmp (x, y) select sum(i), sum(s) from t group by b;
select * from tmp;
drop table tmp;

-- overflow
create table tmp (x int);
insert into tmp values (2147483647),
                     (2147483647);
select sum(x) from tmp;
drop table tmp;

create table tmp (x double precision);
insert into tmp values (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647),
                     (2147483647);
select sum(x) from tmp;
drop table tmp;