Skip to main content

​
GROUP BY

Group rows by one or more columns and apply aggregate functions to each group: 
SELECT status, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY status
Multiple grouping columns: 
SELECT customer_id, status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY customer_id, status
Positional ordinals are also supported. GROUP BY 1, 2 groups by the first and second select-list expressions: 
SELECT customer_id, status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY 1, 2
Grouping expressions are also supported directly: 
SELECT region || ':' || status AS region_status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY region || ':' || status

​
HAVING

Filter groups after aggregation. You can use both aggregate and non-aggregate predicates: 
-- Keep only customers with more than 5 orders
SELECT customer_id, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY customer_id
HAVING COUNT(*) > 5

-- Combine aggregate and non-aggregate conditions
SELECT customer_id, SUM(total) AS total_spent
FROM myschema.orders
GROUP BY customer_id
HAVING SUM(total) > 1000 AND customer_id IS NOT NULL

​
Aggregate Functions

FunctionDescription
COUNT(*)Count all rows in the group
COUNT(col)Count non-NULL values in col
SUM(col)Sum of non-NULL values
AVG(col)Average of non-NULL values
MIN(col)Minimum value
MAX(col)Maximum value
STDDEV(col)Population standard deviation
SELECT
  category,
  COUNT(*) AS total_products,
  COUNT(description) AS with_description,
  AVG(price) AS avg_price,
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  STDDEV(price) AS price_stddev
FROM myschema.products
GROUP BY category

​
DISTINCT Aggregates

Deduplicate values before aggregating: 
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM myschema.orders

SELECT SUM(DISTINCT total) AS sum_unique_totals
FROM myschema.orders

​
Using Aliases in ORDER BY

Aliases defined in the SELECT list are available in ORDER BY. They cannot be used in GROUP BY or HAVING — repeat the expression there instead. 
SELECT customer_id, SUM(total) AS lifetime_value
FROM myschema.orders
GROUP BY customer_id
HAVING SUM(total) > 500
ORDER BY lifetime_value   -- alias is valid here

​
GROUPING SETS

GROUPING SETS lets you compute aggregates for multiple grouping combinations in a single query. Columns not part of a given grouping set are returned as NULL: 
SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY GROUPING SETS((region), (status))

​
ROLLUP

ROLLUP creates subtotals that roll up from the most detailed level to a grand total. ROLLUP(a, b) is equivalent to GROUPING SETS((a, b), (a), ()): 
SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY ROLLUP(region, status)

​
CUBE

CUBE generates subtotals for all possible combinations of the grouping columns. CUBE(a, b) is equivalent to GROUPING SETS((a, b), (a), (b), ()): 
SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY CUBE(region, status)

​
Not Supported

  • Window functions (ROW_NUMBER, RANK, SUM OVER, etc.)