Home / SQL / SUM and AVG

SUM and AVG

Overview

The SUM() and AVG() functions are aggregate functions used to calculate the total sum and average (mean) of numeric values in a column, respectively.

SUM() adds all values together, while AVG() calculates the arithmetic mean of the values. Both functions are essential for financial calculations, statistics, and data analysis.

SUM Function

The SUM() function returns the sum of all values in a numeric column.

SUM Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

AVG Function

The AVG() function returns the average (mean) of all values in a numeric column.

AVG Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Examples

Example 1: Sum of All Values

Total sales

SELECT SUM(total_amount) AS total_sales
FROM Orders;

Example 2: Average Value

Average order amount

SELECT AVG(total_amount) AS average_order_amount
FROM Orders;

Example 3: Sum and Average Together

Multiple aggregates

SELECT 
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS average_order,
    COUNT(*) AS order_count
FROM Orders;

Example 4: Sum with WHERE

Conditional sum

SELECT SUM(quantity * unit_price) AS total_revenue
FROM OrderItems
WHERE order_date >= '2024-01-01';

Example 5: Average with Filtering

Conditional average

SELECT AVG(price) AS average_price
FROM Products
WHERE category = 'Electronics'
  AND price > 100;

SUM and AVG with GROUP BY

Use SUM() and AVG() with GROUP BY to calculate per group:

Sum per category

SELECT 
    category,
    SUM(price * stock_quantity) AS total_value,
    AVG(price) AS average_price
FROM Products
GROUP BY category;

Example Result:

Sample Result

┌──────────────┬─────────────┬───────────────┐
│ category     │ total_value │ average_price │
├──────────────┼─────────────┼───────────────┤
│ Electronics  │ 50000       │ 299.99        │
│ Clothing     │ 15000       │ 49.99         │
│ Books        │ 3000        │ 19.99         │
└──────────────┴─────────────┴───────────────┘

SUM with Expressions

You can use expressions inside SUM():

Sum of calculated values

-- Sum of quantity * price
SELECT SUM(quantity * unit_price) AS total_revenue
FROM OrderItems;

-- Sum with discount
SELECT SUM(quantity * unit_price * (1 - discount)) AS net_revenue
FROM OrderItems;

AVG with ROUND

AVG() often returns many decimal places. Use ROUND() to format:

Rounded average

-- Average with 2 decimal places
SELECT ROUND(AVG(price), 2) AS average_price
FROM Products;

-- Average as integer
SELECT ROUND(AVG(price), 0) AS average_price_rounded
FROM Products;

SUM and AVG with NULL Values

Both SUM() and AVG() ignore NULL values in calculations:

NULL handling

-- NULL values are excluded
SELECT 
    SUM(price) AS total,
    AVG(price) AS average
FROM Products;
-- Only non-NULL prices are included

Sum with COUNT for Statistics

Combine SUM, AVG, COUNT, MIN, MAX for comprehensive statistics:

Complete statistics

SELECT 
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS average_order,
    MIN(total_amount) AS smallest_order,
    MAX(total_amount) AS largest_order
FROM Orders
WHERE order_date >= '2024-01-01';

SUM and AVG with JOIN

Calculate sums and averages from joined tables:

Sum from joined tables

SELECT 
    c.name AS customer_name,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS average_order_amount,
    COUNT(o.order_id) AS order_count
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

SUM with HAVING

Filter groups using HAVING after aggregation:

Filter by sum

SELECT 
    customer_id,
    SUM(total_amount) AS total_spent
FROM Orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;

Common Use Cases

Calculating total sales or revenue
Finding average prices or costs
Calculating totals per category or group
Financial reporting and analysis
Statistical analysis of numeric data

Common Calculations

Percentage of Total

Percentage calculation

SELECT 
    category,
    SUM(price) AS category_total,
    SUM(price) * 100.0 / (SELECT SUM(price) FROM Products) AS percentage
FROM Products
GROUP BY category;

Running Totals

Running total (window function)

SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM Orders
ORDER BY order_date;

Performance Considerations

Indexes help: Indexes on aggregated columns improve performance
Filter first: Use WHERE to reduce rows before aggregation
Use appropriate data types: Numeric types are more efficient for calculations
Consider materialized views: For frequently calculated aggregates

Best Practices

Use aliases: Give meaningful names to SUM/AVG results
Round AVG results: Format decimal places appropriately
Handle NULLs: Be aware NULLs are excluded from calculations
Combine with other aggregates: Get comprehensive statistics
Verify calculations: Double-check financial calculations

Next Steps

Learn about grouping data with GROUP BY, or explore filtering grouped results with HAVING.

← Count Like →