MySQL in a Nutshell, 2nd Edition

Name

SUM()

Synopsis

SUM([DISTINCT] expression)

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 are 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. Here is an example:

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 the last month. From these results, SUM() returns the total sale amounts aggregated by the sales_rep_id (see Grouping SELECT results” under the SELECT statement in Chapter 6).