The SUM() function in MySQL is an aggregate function used to calculate the total sum of a numeric column. It is useful for adding up values across multiple rows in a table and can be used in various scenarios, such as summing sales amounts, calculating total expenses, or aggregating other numeric data.

Syntax

SUM(column_name)

• column_name: The name of the numeric column for which you want to calculate the sum.

Usage

1. Basic Sum Calculation

   To calculate the total sum of values in a column:

   SELECT SUM(salary) AS total_salary
   FROM employees;
   

   This query calculates the total salary of all employees from the employees table and labels the result as total_salary.

2. Sum with Conditions

   To calculate the sum of values based on specific conditions, use the WHERE clause:

   SELECT SUM(amount) AS total_sales
   FROM sales
   WHERE status = 'Completed';
   

   This query calculates the total sales amount where the status is 'Completed'.

3. Sum with Grouping

   To calculate the sum for each group of rows, use the GROUP BY clause:

   SELECT department, SUM(salary) AS total_salary
   FROM employees
   GROUP BY department;
   

   This query calculates the total salary for each department.

4. Sum with HAVING Clause

   To filter groups based on aggregate values, use the HAVING clause:

   SELECT department, SUM(salary) AS total_salary
   FROM employees
   GROUP BY department
   HAVING SUM(salary) > 100000;
   

   This query retrieves departments where the total salary exceeds 100,000.

5. Sum with Multiple Columns

   While SUM() is typically used on a single column, you can sum multiple columns in calculations:

   SELECT SUM(sales_amount) + SUM(tax_amount) AS total_revenue
   FROM sales;
   

   This query calculates the total revenue by summing up both sales amounts and tax amounts.

6. Handling NULL Values

   The SUM() function ignores NULL values in the specified column. If a column contains NULL values, they are simply excluded from the sum calculation:

   SELECT SUM(bonus) AS total_bonus
   FROM employees;
   

   If some bonus values are NULL, they are not included in the sum calculation.

Best Practices

• Use Indexes: Ensure that columns used in aggregate functions are indexed to improve performance, especially in large tables.
• Filter Data: Use the WHERE clause to filter data before applying SUM() for more accurate results based on specific criteria.
• Combine with Other Aggregates: Use SUM() in combination with other aggregate functions like COUNT(), AVG(), and MAX() for comprehensive data analysis.
• Handling Large Datasets: Be aware of performance implications when working with very large datasets. Optimize queries and consider using indexed columns.