--
-- ### Grouping checks
--
 
-- create table t1 ( primary a int not null, b int );
-- alter table t1 add check chk1 on b < 10;
-- drop table t1;

create table t1 ( a int, b string(10), c string(10));

desc table t1;

-- empty table checks
select count(*) from t1;
select sum(a) from t1;
select b, sum(a) from t1 group by b;

insert into t1 values ( 10, 'hugo', 'erwin');
insert into t1 values ( 20, 'hugo', 'ida');
insert into t1 values ( 50, 'hugo', 'goofy');

insert into t1 values ( 20, 'berta', 'goofy');
insert into t1 values ( 10, 'berta', 'goofy');
insert into t1 values ( 100, 'berta', 'goofy');

insert into t1 values ( 100, 'ida', 'goofy');
insert into t1 values ( 100, 'zara', 'goofy');

insert into t1 values ( 1000, 'kuno', 'goofy');
insert into t1 values ( 5, 'kuno', 'goofy');


select b, count(*) from t1 group by b;
select count(*), b from t1 group by b;
select b, c, count(*) from t1 group by b, c;
select b, count(*) from t1 group by b having count(*) = 2;
select b, count(*) from t1 group by b having count(*) + 1 = 3;
select b, 2 * count(*) from t1 group by b having count(*) + 1 = 3;
select b, count(*) from t1 group by b order by count(*);
select b, count(*) from t1 group by b having count(*) < 3 order by count(*);

select b, avg(a) from t1 group by b;

select b, sum(a) from t1 group by b order by sum(a);
select a, b from t1 order by b;
select a, b from t1 order by a;
select a, b from t1 order by a, b;
select b, sum(a) from t1 group by b order by sum(a) desc, b desc;
select b, sum(a) from t1 group by b order by sum(a) desc, b asc;

drop table t1;





