MySQL SUM

The MySQL SUM function is an aggregate function that is used to calculate the sum of values in a numeric column. It is commonly used in conjunction with the GROUP BY clause to perform calculations on grouped data. Here is an overview of the SUM function in MySQL:

Syntax

The basic syntax of the SUM function is as follows:

SELECT SUM(column_name) 
FROM table_name
WHERE condition;

column_name: The name of the column for which you want to calculate the sum.
table_name: The name of the table containing the data.
condition: An optional WHERE clause to filter the rows for the sum calculation.

Example

Consider a table named sales with columns product and amount_sold. You can use the SUM function to calculate the total amount sold for each product:

SELECT product, 
SUM(amount_sold) AS total_amount_sold
FROM sales
GROUP BY product;

In this example, the SUM function is applied to the amount_sold column, and the result is grouped by the product column. The AS total_amount_sold is an alias for the calculated sum, providing a more readable output.

Using with WHERE clause:
You can also use the SUM function with a WHERE clause to calculate the sum for specific conditions:

SELECT SUM(amount_sold) AS total_amount_sold
FROM sales
WHERE product = 'Widget';

This query calculates the total amount sold for the product ‘Widget’.

Handling NULL Values:
It’s important to note that the SUM function ignores NULL values in the specified column. If your column may contain NULL values, and you want to include them in the sum, you can use the IFNULL function or the COALESCE function to replace NULL values with a default value.

SELECT 
SUM(IFNULL(amount_sold, 0)) AS total_amount_sold
FROM sales;

This ensures that NULL values are treated as 0 in the sum calculation.

In summary, the SUM function in MySQL is a powerful tool for calculating the total of numeric values in a column, either for the entire table or for specific groups of data when used with the GROUP BY clause.