MySQL SUM

Sum is a statistical operation that adds a list of values and gives the total . MySQL Sum Function is used to do this aggregate function for numeric data columns in a MySQL data table.  Only one column or expression can be given in MySQL SUM function. If you need to find column wise total of multiple columns then all those columns must be given in separate SUM functions in the SELECT statement.

Syntax of MySQL Sum Function

The Sum function is used in a SELECT query according to the given syntax.

SUM([DISTINCT]  expression|table_column)

  • SUM is the keyword used for adding numeric column data in MySQL SELECT statement.
  • DISTINCT is the keyword. It is optional. When this keyword is used in the query only distinct values from column or expression are added.
  • table_column is the column name of the table whose Sum is to be calculated.
  • Expression is the expression using table column names and/or MySQL Arithmetic Operators.

Examples of MySQL Sum

This data is used in the following examples.

Adding values of one column

The following query displays sum of column billAmt.

SELECT SUM(billAmt) FROM sales;
MySQL Sum one column

Adding values of multiple columns

This query displays sum of columns billAmt and purAmt used in separate SUM functions.

SELECT SUM(billAmt), SUM(purAmt) FROM sales;
Multiple Columns

Adding values of an expression

This query displays sum of columns billAmt , purAmt  and expression ‘billAmt –purAmt’  used in separate SUM functions

SELECT SUM(billAmt), SUM(purAmt),SUM(billAmt-purAmt) FROM sales;
Expression sum

Adding distinct values

To get sum of only distinct values DISTINCT keyword is used in two separate SUM functions for two columns.

SELECT SUM(DISTINCT billAmt), SUM(DISTINCT purAmt) FROM sales;
Distinct Value addition

Adding values with where clause

To get sum of column values that meets a specific condition, WHERE clause is used in the following query.

SELECT SUM(billAmt), SUM(purAmt) FROM sales WHERE sid='S1001';
Sum filtered value

Points to remember

  • You can use one SUM function for a column name or expression. Separate SUM functions are needed for multiple columns or expressions.
  • Sum function can be used only in a SELECT statement.
  • To include only unique values in calculating Sum use DISTINCT keyword within parenthesis before column name or expression.
  • WHERE clause is used to filter rows that meet a certain condition.
  • SUM function always returns a numeric value.
  • This function considers NULL values in columns as zeroes. It returns sum of all the non-null values.
  • If SUM function is assigned a non-numeric column, like string, zero is returned.