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 as SUM, AVG, and COUNT. ()
The is fully supported in Snowflake.
Grammar Syntax
GROUP BY expression [, ...]
Sample Source Patterns
Input Code:
IN -> Redshift_01.sql
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
INTO manager_employees
FROM employee
GROUP BY manager_id
ORDER BY manager_id;
MANAGER_ID
TOTAL_EMPLOYEES
100
1
101
3
102
2
103
3
104
3
1
Output Code:
OUT -> Redshift_01.sql
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": "02/06/2025", "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);
CREATE TABLE IF NOT EXISTS manager_employees AS
SELECT
manager_id,
COUNT(id) AS total_employees
FROM
employee
GROUP BY manager_id
ORDER BY manager_id;
