SQL Aggregate Functions

SQL aggregate functions enable developers to perform basic calculations across multiple rows in a database, answering questions like "how many," "what's the highest," or "what's the average value." In this tutorial, you will learn how to:

• Use aggregate functions such as MAX, MIN, and COUNT.
• Group data using GROUP BY and filter grouped results with HAVING.

What are Aggregate Functions in SQL?

Aggregate functions allow you to perform calculations on a set of rows. The most commonly used functions include:

• COUNT: Counts the number of rows.
• MIN: Finds the smallest value.
• MAX: Finds the largest value.
• SUM or TOTAL: Adds up all values.
• AVG: Calculates the average of values.

Always check your database's documentation to ensure the correct usage of aggregate functions. For SQLite, refer to the SQLite Aggregate Functions.

Using Aggregate Functions

Basic Queries

You can include aggregate functions directly in a SELECT clause. For example:

• Count the number of employees:

  SELECT COUNT(*) AS count 
  FROM employees;

• Find the highest commission:

  SELECT MAX(commission) AS highest_commission 
  FROM commissions;

Using WHERE with Aggregates

To calculate aggregates on a subset of rows, use a WHERE clause:

SELECT COUNT(*) AS count 
FROM employees 
WHERE department = 'Sales';

Grouping Data with GROUP BY

The GROUP BY clause groups rows based on a column and performs aggregate calculations for each group.

Examples:

• Count employees by city:

  SELECT COUNT(id) AS employee_count, city 
  FROM employee_addresses 
  GROUP BY city;

• Calculate average sales per month:

  SELECT AVG(sales_amount) AS avg_sales, month, year 
  FROM sales 
  GROUP BY month, year;

Sorting Aggregates with ORDER BY

You can order query results based on aggregate values using the ORDER BY clause.

Examples:

• Rank cities by the number of orders:

  SELECT COUNT(order_id) AS order_count, city, state 
  FROM orders 
  GROUP BY city, state 
  ORDER BY order_count DESC;

• Find the city with the most orders:

  SELECT city, state 
  FROM orders 
  GROUP BY city, state 
  ORDER BY COUNT(order_id) DESC 
  LIMIT 1;

Filtering Aggregates with HAVING

Use HAVING to filter grouped data based on aggregate values. Unlike WHERE, which filters rows before grouping, HAVING filters groups after aggregation.

Example:

Return cities with more than one order:

SELECT COUNT(order_id) AS order_count, city, state 
FROM orders 
GROUP BY city, state 
HAVING order_count > 1 
ORDER BY order_count DESC;

Real-World Applications

SQL aggregate functions are essential in scenarios such as:

• Business reporting: Summarizing sales, customer activity, or product inventory.
• Data analysis: Calculating averages, totals, and trends over time.
• Performance tracking: Measuring employee performance or system usage metrics.

What You Learned

In this lesson, you learned:

• How to use aggregate functions like COUNT, MIN, MAX, SUM, and AVG.
• How to group data using GROUP BY and filter grouped results using HAVING.
• How to order query results by aggregate values using ORDER BY.

By combining these techniques, you can perform advanced data analysis and generate insightful reports directly from your database.