1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
-- Sum of quantity per dealership. Group by `id`.
SELECT
id,
sum(quantity) AS sum_quantity
FROM dealer GROUP BY id ORDER BY id;
-- Use column position in GROUP by clause.
SELECT
id,
sum(quantity) AS sum_quantity
FROM dealer GROUP BY 1 ORDER BY 1;
-- Multiple aggregations.
-- 1. Sum of quantity per dealership.
-- 2. Max quantity per dealership.
SELECT
id,
sum(quantity) AS sum_quantity,
max(quantity) AS max_quantity
FROM dealer GROUP BY id ORDER BY id;
-- Count the number of distinct dealer cities per car_model.
SELECT
car_model,
count(DISTINCT city) AS count_distinct_city
FROM dealer GROUP BY car_model;
-- Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.
SELECT
id,
sum(quantity) FILTER (
WHERE car_model IN ('Honda Civic', 'Honda CRV')
) AS `sum(quantity)` FROM dealer
GROUP BY id ORDER BY id;
-- Aggregations using multiple sets of grouping columns in a single statement.
-- Following performs aggregations based on four sets of grouping columns.
-- 1. city, car_model
-- 2. city
-- 3. car_model
-- 4. Empty grouping set. Returns quantities for all city and car models.
SELECT
city,
car_model,
sum(quantity) AS sum_quantity
FROM dealer
GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
ORDER BY city;
SELECT
city,
car_model,
sum(quantity) AS sum_quantity
FROM dealer
GROUP BY city, car_model GROUPING SETS ((city, car_model), (city), (car_model), ())
ORDER BY city;
SELECT
city,
car_model,
sum(quantity) AS sum_quantity
FROM dealer
GROUP BY city, car_model, GROUPING SETS ((city, car_model), (city), (car_model), ())
ORDER BY city;
-- Group by processing with `ROLLUP` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())
SELECT
city,
car_model,
sum(quantity) AS sum_quantity
FROM dealer
GROUP BY city, car_model WITH ROLLUP
ORDER BY city, car_model;
-- Group by processing with `CUBE` clause.
-- Equivalent GROUP BY:
-- GROUPING SETS ((city, car_model), (city), (car_model), ())
SELECT
city,
car_model,
sum(quantity) AS sum_quantity
FROM dealer
GROUP BY city, car_model WITH CUBE
ORDER BY city, car_model;
-- Select the first row in column age
-- Implicit GROUP BY
SELECT first(age) FROM person;
-- Implicit GROUP BY
SELECT
first(age IGNORE NULLS) AS first_age,
last(id) AS last_id,
sum(id) AS sum_id
FROM person;
-- CUBE within GROUP BY clause
SELECT
name,
age,
count(*) AS record_count
FROM people
GROUP BY cube(name, age);
-- CUBE within GROUP BY clause with single clause on newline
SELECT
name,
count(*) AS record_count
FROM people
GROUP BY cube(
name
);
-- CUBE within GROUP BY clause with multiple clauses on newline
SELECT
name,
age,
count(*) AS record_count
FROM people
GROUP BY cube(
name,
age
);
-- ROLLUP within GROUP BY clause
SELECT
name,
age,
count(*) AS record_count
FROM people
GROUP BY rollup(name, age);
-- GROUP BY ALL
SELECT
name,
age,
count(*) AS record_count
FROM people
GROUP BY ALL;
|
