modules/calcite/src/test/sql/aggregate/group/test_group_by.test

# name: test/sql/aggregate/group/test_group_by.test
# description: Test aggregation/group by statements
# group: [group]

statement ok
PRAGMA enable_verification

statement ok
CREATE TABLE test (a INTEGER, b INTEGER);

statement ok
INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)

# aggregates cannot be nested
statement error
SELECT SUM(SUM(41)), COUNT(*);

# simple aggregates without group by
query RIR
SELECT SUM(a), COUNT(*), AVG(a) FROM test;
----
36.000000	3	12.000000

query I
SELECT COUNT(*) FROM test;
----
3

query RI
SELECT SUM(a), COUNT(*) FROM test WHERE a = 11;
----
11.000000	1

query RRR
SELECT SUM(a), SUM(b), SUM(a) + SUM (b) FROM test;
----
36.000000	65.000000	101.000000

query RR
SELECT SUM(a+2), SUM(a) + 2 * COUNT(*) FROM test;
----
42.000000	42.000000

# aggregations with group by
query IRRR
SELECT b, SUM(a), SUM(a+2), AVG(a) FROM test GROUP BY b ORDER BY b;
----
21	12.000000	14.000000	12.000000
22	24.000000	28.000000	12.000000

# ORDER BY aggregation that does not occur in SELECT clause
query IR
SELECT b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a);
----
21	12.000000
22	24.000000

query IR
SELECT b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a) DESC;
----
22	24.000000
21	12.000000

query IRIR
SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test GROUP BY b ORDER BY b;
----
21	12.000000	1	14.000000
22	24.000000	2	28.000000

# group by with filter
query IRIR
SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test WHERE a <= 12 GROUP BY b ORDER BY b;
----
21	12.000000	1	14.000000
22	11.000000	1	13.000000

# nested aggregate in group by
statement error
SELECT b % 2 AS f, COUNT(SUM(a)) FROM test GROUP BY f;

# group by alias
query IR
SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER By f;
----
0	24.000000
1	12.000000

statement ok
INSERT INTO test VALUES (12, 21), (12, 21), (12, 21)

# group by with filter and multiple values per groups
query IRIR
SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test WHERE a <= 12 GROUP BY b ORDER BY b;
----
21	48.000000	4	56.000000
22	11.000000	1	13.000000

# group by with filter and multiple values per groups
statement ok
CREATE TABLE integers(i INTEGER, j INTEGER);

statement ok
INSERT INTO integers VALUES (3, 4), (3, 4), (2, 4);

# use GROUP BY column in math operator
query II
SELECT i, i + 10 FROM integers GROUP BY i ORDER BY i
----
2	12
3	13

# using non-group column and non-aggregate should throw an error
statement error
SELECT i, SUM(j), j FROM integers GROUP BY i ORDER BY i

# but it works if we wrap it in ANY_VALUE()
query IRI
SELECT i, SUM(j), ANY_VALUE(j) FROM integers GROUP BY i ORDER BY i
----
2	4.000000	4
3	8.000000	4

# group by constant alias
query IR
SELECT 1 AS k, SUM(i) FROM integers GROUP BY k ORDER BY 2;
----
1	8.000000

# use an alias that is identical to a column name and the table reference
query IR
SELECT 1 AS i, SUM(t.i) FROM integers t GROUP BY t.i ORDER BY 2;
----
1	2.000000
1	6.000000

# use an alias that is identical to a column name
query IR
SELECT 1 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 2;
----
1	8.000000

# refer to the same alias twice
query IR
SELECT i % 2 AS k, SUM(i) FROM integers GROUP BY k, k ORDER BY 1;
----
0	2.000000
1	6.000000

statement ok
DROP TABLE integers;

statement ok
CREATE TABLE integers(i INTEGER);

statement ok
INSERT INTO integers VALUES (1), (2), (3), (NULL);

# group by NULL
query IR
SELECT i, SUM(i) FROM integers GROUP BY i ORDER BY 1 NULLS FIRST;
----
NULL	NULL
1	1.000000
2	2.000000
3	3.000000

# column reference have to be specified when an alias with the same name exists
query IIR
SELECT i, i % 2 AS i, SUM(i) FROM integers t GROUP BY t.i ORDER BY 1 NULLS FIRST;
----
NULL	NULL	NULL
1	1	1.000000
2	0	2.000000
3	1	3.000000

# aliases can only be referenced in the GROUP BY as the root column: operations not allowed
# CONTROVERSIAL: this query DOES work in SQLite
statement error
SELECT 1 AS k, SUM(i) FROM integers GROUP BY k+1 ORDER BY 2;

# group by column refs should be recognized, even if one uses an explicit table specifier and the other does not
query II
SELECT test.b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a) DESC;
----
21	48
22	24