-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 15
-- !query 0
CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
(1, 1), (1, 2), (2, 1), (2, 2), (3, 1), (3, 2), (null, 1), (3, null), (null, null)
AS testData(a, b)
-- !query 0 schema
struct<>
-- !query 0 output
-- !query 1
SELECT a, COUNT(b) FROM testData
-- !query 1 schema
struct<>
-- !query 1 output
org.apache.spark.sql.AnalysisException
grouping expressions sequence is empty, and 'testdata.`a`' is not an aggregate function. Wrap '(count(testdata.`b`) AS `count(b)`)' in windowing function(s) or wrap 'testdata.`a`' in first() (or first_value) if you don't care which value you get.;
-- !query 2
SELECT COUNT(a), COUNT(b) FROM testData
-- !query 2 schema
struct<count(a):bigint,count(b):bigint>
-- !query 2 output
7 7
-- !query 3
SELECT a, COUNT(b) FROM testData GROUP BY a
-- !query 3 schema
struct<a:int,count(b):bigint>
-- !query 3 output
1 2
2 2
3 2
NULL 1
-- !query 4
SELECT a, COUNT(b) FROM testData GROUP BY b
-- !query 4 schema
struct<>
-- !query 4 output
org.apache.spark.sql.AnalysisException
expression 'testdata.`a`' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.;
-- !query 5
SELECT COUNT(a), COUNT(b) FROM testData GROUP BY a
-- !query 5 schema
struct<count(a):bigint,count(b):bigint>
-- !query 5 output
0 1
2 2
2 2
3 2
-- !query 6
SELECT 'foo', COUNT(a) FROM testData GROUP BY 1
-- !query 6 schema
struct<foo:string,count(a):bigint>
-- !query 6 output
foo 7
-- !query 7
SELECT 'foo' FROM testData WHERE a = 0 GROUP BY 1
-- !query 7 schema
struct<foo:string>
-- !query 7 output
-- !query 8
SELECT 'foo', APPROX_COUNT_DISTINCT(a) FROM testData WHERE a = 0 GROUP BY 1
-- !query 8 schema
struct<foo:string,approx_count_distinct(a):bigint>
-- !query 8 output
-- !query 9
SELECT 'foo', MAX(STRUCT(a)) FROM testData WHERE a = 0 GROUP BY 1
-- !query 9 schema
struct<foo:string,max(named_struct(a, a)):struct<a:int>>
-- !query 9 output
-- !query 10
SELECT a + b, COUNT(b) FROM testData GROUP BY a + b
-- !query 10 schema
struct<(a + b):int,count(b):bigint>
-- !query 10 output
2 1
3 2
4 2
5 1
NULL 1
-- !query 11
SELECT a + 2, COUNT(b) FROM testData GROUP BY a + 1
-- !query 11 schema
struct<>
-- !query 11 output
org.apache.spark.sql.AnalysisException
expression 'testdata.`a`' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.;
-- !query 12
SELECT a + 1 + 1, COUNT(b) FROM testData GROUP BY a + 1
-- !query 12 schema
struct<((a + 1) + 1):int,count(b):bigint>
-- !query 12 output
3 2
4 2
5 2
NULL 1
-- !query 13
SELECT SKEWNESS(a), KURTOSIS(a), MIN(a), MAX(a), AVG(a), VARIANCE(a), STDDEV(a), SUM(a), COUNT(a)
FROM testData
-- !query 13 schema
struct<skewness(CAST(a AS DOUBLE)):double,kurtosis(CAST(a AS DOUBLE)):double,min(a):int,max(a):int,avg(a):double,var_samp(CAST(a AS DOUBLE)):double,stddev_samp(CAST(a AS DOUBLE)):double,sum(a):bigint,count(a):bigint>
-- !query 13 output
-0.2723801058145729 -1.5069204152249134 1 3 2.142857142857143 0.8095238095238094 0.8997354108424372 15 7
-- !query 14
SELECT COUNT(DISTINCT b), COUNT(DISTINCT b, c) FROM (SELECT 1 AS a, 2 AS b, 3 AS c) GROUP BY a
-- !query 14 schema
struct<count(DISTINCT b):bigint,count(DISTINCT b, c):bigint>
-- !query 14 output
1 1
