SQL Aggregate Functions
Use SQL aggregation functions to perform calculations on a group of rows. It will help you provide meaningful insights into the data set. For example, in a table of sales transactions, you could use SQL aggregation functions to estimate the total sales revenue, the average sales price, or the number of sales for a particular product.
Here are some scenarios for SQL aggregation functions:
- Calculate the total or average value of a specific column across all rows in a table.
- Group rows based on specific criteria and then calculate summary values for each group.
- Filter rows based on summary values.
- Combine multiple tables and calculate summary values across the combined data set.
Here are the standard SQL aggregation functions:
COUNT: Returns the total of rows in a table or the number of non-NULL values in a column. It is the only function that considers NULL values.
SUM: Returns the sum of all non-NULL values in a column.
AVG: Returns the average of all non-NULL values in a column.
MIN: Returns the minimum non-NULL value in a column.
MAX: Returns the maximum non-NULL value in a column.
These two clauses are used in conjunction with aggregate functions to shape their behavior
GROUP BY: Performs the aggregation on sets of values in each group.
HAVING: Limits the results of a query based on the resulting aggregate value for each group.
Aggregation functions are often used with the GROUP BY clause to group the records by a specific column or set of columns. In Coginiti, these functions can be set with our drag and drop tool in the Grid View, as seen in the image below. Along this tutorial, you’ll meet examples of each aggregate function.