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

​
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

​
Not Supported

  • GROUP BY with expressions (e.g., GROUP BY YEAR(created_at))
  • Positional GROUP BY ordinals (e.g., GROUP BY 1, 2)
  • GROUPING SETS, ROLLUP, CUBE
  • Window functions (ROW_NUMBER, RANK, SUM OVER, etc.)