GROUP BY clause
Description
The
GROUP BY
clause identifies the grouping columns for the query. Grouping columns must be declared when the query computes aggregates with standard functions such asSUM
,AVG
, andCOUNT
. (Redshift SQL Language Reference GROUP BY Clause)
The GROUP BY clause is fully supported in Snowflake.
Grammar Syntax
GROUP BY group_by_clause [, ...]
group_by_clause := {
expr |
GROUPING SETS ( () | group_by_clause [, ...] ) |
ROLLUP ( expr [, ...] ) |
CUBE ( expr [, ...] )
}
Sample Source Patterns
Grouping sets
Input Code:
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
SELECT
manager_id,
COUNT(id) AS total_employees
FROM employee
GROUP BY GROUPING SETS
((manager_id), ())
ORDER BY manager_id;
Output Code:
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "11/05/2024", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
SELECT
manager_id,
COUNT(id) AS total_employees
FROM
employee
GROUP BY GROUPING SETS
((manager_id), ())
ORDER BY manager_id;
Group by Cube
Input Code:
SELECT
manager_id,
COUNT(id) AS total_employees
FROM
employee
GROUP BY CUBE(manager_id)
ORDER BY manager_id;
Output Code:
SELECT
manager_id,
COUNT(id) AS total_employees
FROM
employee
GROUP BY CUBE(manager_id)
ORDER BY manager_id;
Group by Rollup
Input Code:
SELECT
manager_id,
COUNT(id) AS total_employees
FROM
employee
GROUP BY ROLLUP(manager_id)
ORDER BY manager_id;
Output Code:
SELECT
manager_id,
COUNT(id) AS total_employees
FROM
employee
GROUP BY ROLLUP(manager_id)
ORDER BY manager_id;
Related EWIs
There are no known issues.
Last updated