SUM( )

This MySQL function returns the sum of the values for the given column or expression.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SUM([DISTINCT] expression)

Explanation

This function returns the sum of the values for the given column or expression. It's normally used in conjunction with a GROUP BY clause specifying a unique column, so that values are compared for each unique item separately. It returns NULL if no matching rows were found. The parameter DISTINCT may be given within the parentheses of the function to add only unique values found for a given column. This parameter was added in version 5.1 of MySQL.

Examples

SELECT sales_rep_id,
SUM(sale_amount) AS total_sales
FROM sales 
WHERE DATE_FORMAT(date_of_sale, '%Y%m') = 
      DATE_FORMAT(SUBDATE(CURDATE(), INTERVAL 1 MONTH), '%Y%m')
GROUP BY sales_rep_id;

This statement queries the sales table to retrieve only sales made during last month (see the WHERE clause). From these results, SUM( ) returns the total sale amounts aggregated together by the sales_rep_id (see the GROUP BY clause).