drop table t0 if exists;
create table t0(c varchar(20), i integer, d date);
insert into t0 values ('first', 1, '2000-01-01');
insert into t0 values ('second', 2, '2000-01-02');
insert into t0 values ('third', 3, '2000-02-01');
select year(d), month(d), day(d) from t0;
/*r
 2000,1,1
 2000,1,2
 2000,2,1
*/select year(d) y, month(d) m, day(d) d from t0 group by year(d), month(d), day(d) order by 1, 2, 3;
--
drop table test if exists;
create table test (sel int, name1 varchar(3), name2 varchar(3));
insert into test (sel, name1, name2) values (0, 'foo', 'bar')
insert into test (sel, name1, name2) values (1, 'baz', 'foo')
insert into test (sel, name1, name2) values (1, 'foo', 'qux')
-- select expressions are all composed of columns of group by
/*r
 baz,foo,1
 foo,bar,1
 foo,qux,1
*/select a.name1, a.name2,
  count(a.name1) as counter from test a group by sel, name1, name2 order by 1, 2, 3
/*r
 foo,1
 foo,1
 qux,1
*/select case when a.sel=1 then a.name2 else a.name1 end as name,
  count(a.name1) as counter from test a group by sel, name1, name2
  order by 1, 2
--
/*r
 foo,2
 qux,1
*/select case when a.sel=1 then a.name2 else a.name1 end as name,
  count(a.name1) as counter from test a group by case when a.sel=1
  then a.name2 else a.name1 end order by 1, 2
--
/*r
 qux,1
 foo,2
*/select case when a.sel=1 then a.name2 else a.name1 end as name,
  count(a.name1) as counter from test a group by case when a.sel=1
  then a.name2 else a.name1 end order by counter, name
--
-- order by must be in group or an aggregate
/*r
 foo,bar
 baz,foo
 foo,qux
*/select a.name1, a.name2 from test a group by name1, name2 order by count(a.name1), name2

/*e*/select a.name1, a.name2 from test a group by sel, name1 order by count(a.name1)

/*e*/select a.name1, a.name2
  from test a group by sel, name1 order by a.name1

alter table test add constraint const primary key(name1, name2)

/*r
 foo,bar,0
 baz,foo,1
 foo,qux,1
*/select name1, name2, sel from test group by name1, name2 order by sel

select coalesce(a.name1, a.name2) as name,count(a.sel) as counter from test a
 group by coalesce(a.name1, a.name2)

select * from (select case when a.sel=1 then a.name2 else a.name1 end as name,
  count(a.name1) as counter from test a group by sel, name1, name2)


drop table test;

set database sql references false
drop table trades_a if exists;
create table trades_a(opened timestamp, userid int, points int);
insert into trades_a values ('2017-12-28', 1, 10);

CREATE VIEW trades_scores AS SELECT (EXTRACT(YEAR FROM
 opened) || '-' || EXTRACT(MONTH FROM opened) || '-' ||
 EXTRACT(DAY FROM opened)) AS date, userid, SUM(points)
 AS score FROM trades_a GROUP BY date, userid

/*r
 2017-12-28,1,10*/SELECT (EXTRACT(YEAR FROM
 opened) || '-' || EXTRACT(MONTH FROM opened) || '-' ||
 EXTRACT(DAY FROM opened)) AS date, userid, SUM(points)
 AS score FROM trades_a GROUP BY date, userid

/*r
 2017-12-28,1,10*/SELECT (EXTRACT(YEAR FROM
 opened) || '-' || EXTRACT(MONTH FROM opened) || '-' ||
 EXTRACT(DAY FROM opened)) AS date, userid, SUM(points)
 AS score FROM trades_a GROUP BY (EXTRACT(YEAR FROM
 opened) || '-' || EXTRACT(MONTH FROM opened) || '-' ||
 EXTRACT(DAY FROM opened)), userid

/*r
 2017-12-28,1,10*/select * from trades_scores;

/*o*/SELECT AVG(points) AS VALUE FROM trades_a GROUP BY TIMESTAMP(UNIX_TIMESTAMP(opened) - mod ( UNIX_TIMESTAMP(opened), 60));

/*r
 10*/SELECT AVG(points) AS VALUE FROM trades_a GROUP BY TIMESTAMP(UNIX_TIMESTAMP(opened) - mod ( UNIX_TIMESTAMP(opened), 60));

drop table trades_a cascade;

set database sql references true
--
drop table PROCESSDETAIL if exists;

create table PROCESSDETAIL (
 ID bigint not null,
 ALERTLEVEL integer not null,
 VALUE varchar(255) not null,
 TSTAMP timestamp not null,
 ATTRIBUTEKEY integer not null,
 PROCESSID bigint not null,
 primary key (ID)
 );


-- testdata (not needed to show this problem)
insert into PROCESSDETAIL (ALERTLEVEL, VALUE, TSTAMP, ATTRIBUTEKEY, PROCESSID, ID)
 values (1, 66, '2007-01-01 08:00:00', 28, 9, 1);

insert into PROCESSDETAIL (ALERTLEVEL, VALUE, TSTAMP, ATTRIBUTEKEY, PROCESSID, ID)
 values (0, 67, '2007-01-01 07:59:40', 28, 9, 2);

insert into PROCESSDETAIL (ALERTLEVEL, VALUE, TSTAMP, ATTRIBUTEKEY, PROCESSID, ID)
 values (0, 67, '2007-01-01 06:59:40', 28, 9, 3);

select * from PROCESSDETAIL t1 where
 exists (
 select
 t2.PROCESSID,
 t2.ATTRIBUTEKEY
 from
 PROCESSDETAIL t2
 where
 t2.PROCESSID=t1.PROCESSID
 and t2.ATTRIBUTEKEY=t1.ATTRIBUTEKEY
 and t2.PROCESSID=9
 and t2.TSTAMP<='2007-01-01 08:00:00'
 group by
 t2.PROCESSID ,
 t2.ATTRIBUTEKEY
 having
 max(t2.TSTAMP)=t1.TSTAMP);

select case when 1<0 then 1 else sum(10) end as res from processdetail;

DROP TABLE T1 IF EXISTS
DROP TABLE T2 IF EXISTS
CREATE TABLE T1 (COL1 INT, COL2 INT)
CREATE TABLE T2 (COLA INT, COL2 INT, COLC INT, COLD DATETIME)

INSERT INTO T1 VALUES (101, 201)
INSERT INTO T1 VALUES (102, 201)
INSERT INTO T1 VALUES (103, 202)
INSERT INTO T1 VALUES (106, 202)
INSERT INTO T1 VALUES (104, 203)
INSERT INTO T1 VALUES (105, 203)

INSERT INTO T2 VALUES (1001, 101, 101, '2007-08-10 10:00:00.0')
INSERT INTO T2 VALUES (1002, 102, 101, '2007-08-10 10:00:00.0')
INSERT INTO T2 VALUES (1003, 103, 101, '2007-08-10 10:00:00.0')
INSERT INTO T2 VALUES (1004, 104, 101, '2007-08-10 10:00:00.0')
INSERT INTO T2 VALUES (1004, 105, 101, '2007-08-10 10:00:00.0')

SELECT T1A.COL1, T2A.COLA, MIN(T1A.COL2) AS COL2 FROM
 T1 T1A INNER JOIN T2 T2A ON T1A.COL2 = T2A.COL2
 WHERE T2A.COLD =
 (SELECT MIN(T2B.COLD) FROM
 T1 T1B INNER JOIN T2 T2B ON T1B.COL2 = T2B.COL2
 WHERE T1A.COL1 = T1B.COL1 AND T2A.COLA = T2B.COLA AND T2A.COLC = T2B.COLC)
 GROUP BY
 T1A.COL1, T2A.COLA

CREATE TABLE DATE_TEST (DATECOL DATE)

INSERT INTO DATE_TEST VALUES ('2006-01-01')
INSERT INTO DATE_TEST VALUES ('2007-01-01')
INSERT INTO DATE_TEST VALUES ('2008-01-01')

SELECT * FROM DATE_TEST
SELECT * FROM DATE_TEST WHERE DATECOL > '2007-01-01'
SELECT * FROM DATE_TEST GROUP BY DATECOL HAVING DATECOL > '2007-01-01'
/*c1*/SELECT MAX(DATECOL) FROM DATE_TEST HAVING MAX(DATECOL) > '2007-01-01'
/*c1*/SELECT MAX(DATECOL) FROM DATE_TEST HAVING MAX(DATECOL) < LOCALTIMESTAMP
create table hist (mydate date, files int, space int);
insert into hist values (current_date, 1, 10);
select SUBSTR(CAST (MYDATE AS VARCHAR(20)),1,10), SUM(FILES), SUM(SPACE)
 from hist
 group by SUBSTR(CAST (MYDATE AS VARCHAR(20)),1,10)

---
CREATE TABLE group_by_bug
 ( order_id INTEGER NOT NULL
 , item_id INTEGER NOT NULL
 , price FLOAT NOT NULL
 , tax_rate FLOAT NOT NULL
 , quantity INTEGER NOT NULL );

INSERT INTO group_by_bug ( order_id, item_id, price, tax_rate, quantity )
 VALUES ( 1000, 100, 10, 10, 5 );
INSERT INTO group_by_bug ( order_id, item_id, price, tax_rate, quantity )
 VALUES ( 1001, 100, 10.45, 15, 3 );

/*r
 100,9.09E0,8
*/SELECT item_id, ROUND(price / (1+(tax_rate/100)),2) AS price_net,
 SUM(quantity) AS quantity
 FROM group_by_bug
 GROUP BY item_id, ROUND(price / (1+(tax_rate/100)),2)

/*r
 100,9.09E0,8
*/SELECT item_id, ROUND(price / (1+(tax_rate/100)),2) AS price, SUM(quantity)
 AS quantity
 FROM group_by_bug
 GROUP BY item_id, ROUND(price / (1+(tax_rate/100)),2)

SHUTDOWN COMPACT;

