SQLite sum() function

Description

The sum() and total() aggregate functions return the sum of all non-NULL values in the group. If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it and most other SQL database engines implement sum() that way so SQLite does it in the same way in order to be compatible. The non-standard total() function is provided as a convenient way to work around this design problem in the SQL language.

The result of total() is always a floating point value. The result of sum() is an integer value if all non-NULL inputs are integers. If any input to sum() is neither an integer or a NULL then sum() returns a floating point value which might be an approximation to the true sum.

sum() will throw an "integer overflow" exception if all inputs are integers or NULL and an integer overflow occurs at any point during the computation. total() never throws an integer overflow.

Syntax

sum(expr);
total(expr);

Where expr is an expression.

The DISTINCT keyword can be used to sum only the distinct values of expr.

Example: SQLite sum() function

The following SQLite statement returns the sum of 'total_cost' from purchase table.

Sample table: purchase

Sample Output:

sqlite> SELECT SUM(total_cost)
 ...> FROM purchase;
 SUM(total_cost)
 ---------------
 3590 

Example: Comparing SQLite sum() and total()

The following SQLite statements return the sum of 'total_cost' from purchase table for the category ('cate_id') using sum() and total() function.

Sample table: purchase

Using sum() it returns NULL.

Sample Output:

sqlite> SELECT SUM(total_cost)
 ...> FROM purchase
 ...> WHERE cate_id='CD001';
 SUM(total_cost)
 ---------------

Using total() it returns 0.0.

Sample Output:

sqlite> SELECT total(total_cost)
  ...> FROM purchase
  ...> WHERE cate_id='CD001';
  total(total_cost)
  -----------------
  0.0
 ---------------
 

Example: SQLite sum() function using multiple columns

SQLite sum() function retrieves the sum value of an expression which is made up of more than one columns. The following SQLite statement returns the sum of multiplication of 'receive_qty' and 'purch_price' from purchase table for each group of category ('cate_id') .

Sample table: purchase

Code

Sample Output:

sqlite> SELECT cate_id,
  ...> SUM(receive_qty*purch_price)
  ...> FROM purchase
  ...> GROUP BY cate_id;
  cate_id     SUM(receive_qty*purch_price)
  ----------  ----------------------------
  CA001       1725
  CA002       965
  CA003       900

Example: SQLite sum() function with count() function and variables

The following SQLite statement will return the sum of the ‘mysum’, a temporary variable which counts number of books containing more than 200 pages from 'book_mast' table.

Sample table: book_mast

sqlite> SELECT SUM(mysum)
  ...> FROM(
  ...> SELECT COUNT(*) AS  mysum
  ...> FROM book_mast
  ...> WHERE no_page>200) AS bb;
  SUM(mysum)
  ----------
  14

Sample Output:

Example: SQLite sum() function with DISTINCT clause 

SQLite sum() function retrieves the sum of unique value of an expression if it is accompanied by DISTINCT clause. The following SQLite statement returns the sum of number of branches ('no_of_branch') from publisher table, where, if more than one publisher has same number of branches, that number (i.e. number of branches) is taken once only.

Sample table: publisher

Code

Sample Output:

sqlite> SELECT SUM(DISTINCT no_of_branch)
  ...> FROM publisher;
  SUM(DISTINCT no_of_branch)
  --------------------------
  64

Example: SQLite sum() function with GROUP BY clause

SQLite sum() function retrieves the sum value of an expression which has undergone a grouping operation by GROUP BY clause. The following SQLite statement returns the sum of 'total_cost' from purchase table for each group of category ('cate_id') .

Sample table: purchase

Sample Output:

sqlite> SELECT cate_id,SUM(total_cost)
  ...> FROM purchase
  ...> GROUP BY cate_id;
  cate_id     SUM(total_cost)
  ----------  ---------------
  CA001       1725
  CA002       965
  CA003       900  

Previous: Min()
Next: SQLite Core Function abs()