Constructing Calculations Within a Database Using SQL's Aggregate Functions
Understanding Aggregate Functions
- Aggregate functions are SQL commands that perform calculations on a set of values and return a single result.
- They are essential for summarizing data, making them invaluable for reporting and decision-making.
- Aggregate functions operate on columns of data, not individual rows.
- They are often used with the GROUP BY clause to organize data into meaningful categories.
Key Aggregate Functions
- AVERAGE (AVG): Calculates the mean of a numeric column.
- COUNT: Returns the number of rows that match a specific criterion.
- MAX: Finds the highest value in a column.
- MIN: Finds the lowest value in a column.
- SUM: Calculates the total of a numeric column.
- When using aggregate functions, remember that they ignore NULL values by default.
- This ensures that calculations are based only on valid data.
Using Aggregate Functions in SQL
Let's explore how to use these functions with practical examples.
AVERAGE (AVG)
The AVG function calculates the average of a numeric column.
SELECT AVG(Purchase.Quantity) AS AverageQuantity FROM Purchase;
NoteThis query returns the average number of items purchased per transaction.
COUNT
The COUNT function returns the number of rows that match a specific criterion.
SELECT COUNT(ItemID) AS TotalItems FROM Purchase;
NoteThis query counts the total number of items in the Purchase table, including duplicates.
To count unique items, use COUNT(DISTINCT ...):
SELECT COUNT(DISTINCT ItemID) AS UniqueItems FROM Purchase;
Common Mistake- Don't confuse COUNT(*) with COUNT(column_name).
- The former counts all rows, while the latter counts non-NULLvalues in a specific column.
