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()
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sqlite/aggregate-functions-and-grouping-sum.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics