Aggregate functions operate on values across rows to perform mathematical calculations such as sum, average, counting, minimum/maximum values, standard deviation, and estimation, as well as some non-mathematical operations.
An aggregate function takes multiple rows (actually, zero, one, or more rows) as input and produces a single output. In contrast, scalar functions take one row as input and produce one row (one value) as output.
An aggregate function always returns exactly one row, ***even when the input contains zero rows***. Typically, if the input contained zero rows, the output is NULL. However, an aggregate function could return 0, an empty string, or some other value when passed zero rows.
Snowflake provides a variety of aggregation functions that allow you to perform calculations and summarizations on data. Here are some commonly used aggregation functions in Snowflake:
1. SUM: Calculates the sum of a numeric column.
Example: **`SUM(sales_amount)`** calculates the total sales amount.
2. AVG: Calculates the average (mean) of a numeric column.
Example: **`AVG(product_rating)`** calculates the average rating of products.
3. MIN: Returns the minimum value in a column.
Example: **`MIN(order_date)`** returns the earliest order date.
4. MAX: Returns the maximum value in a column.
Example: **`MAX(order_date)`** returns the latest order date.
5. COUNT: Counts the number of non-null values in a column.
Example: **`COUNT(customer_id)`** counts the number of unique customer IDs.
6. GROUP BY: Groups rows based on one or more columns and performs aggregations on each group.
Example: **`SELECT category, SUM(sales_amount) FROM sales_table GROUP BY category`** calculates the total sales amount for each category.
7. DISTINCT: Returns the unique values in a column.
Example: **`SELECT DISTINCT product_name FROM products`** retrieves the unique product names.
8. COUNT DISTINCT: Counts the number of unique values in a column.
Example: **`COUNT(DISTINCT customer_id)`** counts the number of distinct customer IDs.
9. GROUPING SETS: Performs multiple groupings in a single query, generating subtotals and grand totals.
Example: **`SELECT category, city, SUM(sales_amount) FROM sales_table GROUP BY GROUPING SETS ((category), (city), ())`** calculates subtotals by category, by city, and grand total.
10. HAVING: Filters groups based on aggregate conditions.
Example: **`SELECT category, SUM(sales_amount) FROM sales_table GROUP BY category HAVING SUM(sales_amount) > 10000`** retrieves categories with total sales amount greater than 10,000.
These are just a few examples of the aggregation functions available in Snowflake. Snowflake also supports functions like STDDEV, VARIANCE, MEDIAN, FIRST_VALUE, LAST_VALUE, and more for advanced statistical and windowing calculations. The Snowflake documentation provides a comprehensive list of aggregation functions with detailed explanations and usage examples.
