Overview of SUM() with SQL

Overview of SUM()

The SUM() function in SQL is used to calculate the total sum of values in a specified column. It is an essential aggregation function when you want to obtain totals, such as total sales, cumulative salaries, and so on.

Syntax

Calculate the Total Sum of All Values in a Column 

SELECT SUM(column_name)
FROM table;

SUM(column_name) calculates the sum of all values in the specified column.

Calculate the Sum with Conditions 

SELECT SUM(column_name)
FROM table
WHERE condition;

 SUM(column_name) calculates the sum of values in the specified column that meet the given condition.

Calculate the Sum within Groups of Data 

SELECT grouping_column, SUM(column_name)
FROM table
GROUP BY grouping_column;

SUM(column_name) calculates the sum of values in the specified column for each group defined by the grouping column.

Examples of Using SUM()

Example 1: Calculating the Total Sum of a Column

Assume you have a table sales with columns id, salesperson, and amount: 

CREATE TABLE sales (
    id INT PRIMARY KEY,
    salesperson VARCHAR(100),
    amount DECIMAL(10, 2)
);

To calculate the total amount of sales: 

SELECT SUM(amount) AS total_sales
FROM sales;

Explanation:

• SUM(amount) calculates the total sum of all values in the amount column.

Example 2: Calculating the Sum with Conditions

To calculate the total sales amount made by a specific salesperson: 

SELECT SUM(amount) AS total_sales_person
FROM sales
WHERE salesperson = 'Alice';

Explanation:

• SUM(amount) calculates the sum of the amount column for rows where salesperson is ‘Alice’.
• The WHERE clause filters the rows to include only those where salesperson is ‘Alice’.

Example 3: Calculating the Sum within Groups of Data

If you want to find out the total sales amount for each salesperson: 

SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson;

Explanation:

• SUM(amount) calculates the total sales amount for each salesperson.
• GROUP BY salesperson groups the results by the salesperson column, allowing SUM() to compute the total for each group.

Example 4: Calculating Monthly Sales Totals

Suppose you have a table sales with an additional sale_date column. You can calculate the total sales amount for each month as follows: 

SELECT EXTRACT(MONTH FROM sale_date) AS month, SUM(amount) AS monthly_total_sales
FROM sales
GROUP BY EXTRACT(MONTH FROM sale_date);

Explanation:

• EXTRACT(MONTH FROM sale_date) extracts the month from the sale date.
• SUM(amount) calculates the total amount of sales for each month.
• GROUP BY EXTRACT(MONTH FROM sale_date) groups the results by month.

Key Points

• Non-Numeric Columns: The SUM() function can only be used on numeric columns. Non-numeric or NULL values in the column are ignored in the calculation.
• NULL Values: NULL values in the column are not included in the sum calculation. Only non-NULL values are added.
• Performance: Using SUM() on large tables, especially with GROUP BY clauses and complex filters, can be resource-intensive. Ensure your queries are optimized for performance.

Conclusion

The SUM() function is extremely useful for obtaining totals and aggregations in your SQL data. Whether you need a global total, conditional totals, or grouped totals, SUM() provides a straightforward way to calculate the necessary sums.