How to GROUP BY in Snowflake

How do you use GROUP BY in Snowflake to aggregate data?

GROUP BY clusters rows sharing the same column values so aggregate functions return one result per group in Snowflake.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does GROUP BY do in Snowflake?

GROUP BY collects rows with identical column values into groups, letting aggregate functions like COUNT(), SUM(), and AVG() return one result per group.

How do I write a basic GROUP BY query?

Place non-aggregated columns after GROUP BY. Each selected column must be in either an aggregate or the GROUP BY list.

Example: total spend per customer

SELECT customer_id, SUM(total_amount) AS total_spend FROM Orders GROUP BY customer_id;

How can I aggregate by multiple columns?

Add extra columns to the GROUP BY list. Snowflake returns one row for every unique combination.

Example: revenue per customer per month

SELECT customer_id, DATE_TRUNC('month', order_date) AS month, SUM(total_amount) AS revenue FROM Orders GROUP BY customer_id, month;

How do I filter groups with HAVING?

Use HAVING after GROUP BY to keep or discard whole groups based on aggregate results.

Example: customers with >$1,000 lifetime spend

SELECT customer_id, SUM(total_amount) AS total_spend FROM Orders GROUP BY customer_id HAVING total_spend > 1000;

When should I use GROUPING SETS, ROLLUP, or CUBE?

Use these extensions for multi-level totals in one pass. ROLLUP adds subtotals, CUBE adds all dimension combinations, and GROUPING SETS gives full control.

Best practices for performance

• Limit selected columns to those you need.
• Pre-aggregate heavy datasets in materialized views.
• Use clustering keys on high-cardinality GROUP BY columns for faster scans.

Common mistakes

See the next section for fixes.

Why How to GROUP BY in Snowflake is important

How to GROUP BY in Snowflake Example Usage


-- Average order value per customer in the last 90 days
SELECT c.id, c.name,
       ROUND(AVG(o.total_amount),2) AS avg_order_value
FROM   Customers c
JOIN   Orders o ON o.customer_id = c.id
WHERE  o.order_date >= DATEADD(day,-90,CURRENT_DATE())
GROUP BY c.id, c.name
HAVING COUNT(o.id) >= 2
ORDER BY avg_order_value DESC;

How to GROUP BY in Snowflake Syntax


SELECT <grouping_column1>, <grouping_column2>,
       <aggregate_function>(<target_column>) AS alias
FROM   <table_name>
[WHERE  <filter_condition>]
GROUP BY <grouping_column1>, <grouping_column2>
[HAVING <aggregate_condition>]
[ORDER BY <expr>]

-- Ecommerce example: total items sold per product
SELECT p.id, p.name, SUM(oi.quantity) AS items_sold
FROM   Products p
JOIN   OrderItems oi ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY items_sold DESC;

Common Mistakes

Including columns in SELECT that aren’t in GROUP BY or an aggregate. Snowflake throws SQL compilation error. Fix by either aggregating the column or adding it to GROUP BY.
Filtering with WHERE on aggregated results. WHERE runs before aggregation, so the condition fails. Use HAVING for post-aggregation filters instead.