SQLite sum function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

  1. 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

invoice_no invoice_dt ord_no ord_date receive_dt book_id book_name pub_lang cate_id receive_qty purch_price total_cost
INV0001 2008-07-15 ORD/08-09/0001 2008-07-06 2008-07-19 BK001 Introduction to Electrodynamics English CA001 15 75.00 1125.00
INV0002 2008-08-25 ORD/08-09/0002 2008-08-09 2008-08-28 BK004 Transfer of Heat and Mass English CA002 8 55.00 440.00
INV0003 2008-09-20 ORD/08-09/0003 2008-09-15 2008-09-23 BK005 Conceptual Physics   CA001 20 20.00 400.00
INV0004 2007-08-30 ORD/07-08/0005 2007-08-22 2007-08-30 BK004 Transfer of Heat and Mass English CA002 15 35.00 525.00
INV0005 2007-07-28 ORD/07-08/0004 2007-06-25 2007-07-30 BK001 Introduction to Electrodynamics English CA001 8 25.00 200.00
INV0006 2007-09-24 ORD/07-08/0007 2007-09-20 2007-09-30 BK003 Guide to Networking Hindi CA003 20 45.00 900.00
  1. sqlite> SELECT SUM(total_cost)  
  2.  ...> FROM purchase;  
  3.  SUM(total_cost)  
  4.  ---------------  
  5.  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

invoice_no invoice_dt ord_no ord_date receive_dt book_id book_name pub_lang cate_id receive_qty purch_price total_cost
INV0001 2008-07-15 ORD/08-09/0001 2008-07-06 2008-07-19 BK001 Introduction to Electrodynamics English CA001 15 75.00 1125.00
INV0002 2008-08-25 ORD/08-09/0002 2008-08-09 2008-08-28 BK004 Transfer of Heat and Mass English CA002 8 55.00 440.00
INV0003 2008-09-20 ORD/08-09/0003 2008-09-15 2008-09-23 BK005 Conceptual Physics   CA001 20 20.00 400.00
INV0004 2007-08-30 ORD/07-08/0005 2007-08-22 2007-08-30 BK004 Transfer of Heat and Mass English CA002 15 35.00 525.00
INV0005 2007-07-28 ORD/07-08/0004 2007-06-25 2007-07-30 BK001 Introduction to Electrodynamics English CA001 8 25.00 200.00
INV0006 2007-09-24 ORD/07-08/0007 2007-09-20 2007-09-30 BK003 Guide to Networking Hindi CA003 20 45.00 900.00

Using sum() it returns NULL.

  1. sqlite> SELECT SUM(total_cost)  
  2.  ...> FROM purchase  
  3.  ...> WHERE cate_id='CD001';  
  4.  SUM(total_cost)  
  5.  ---------------  

Using total() it returns 0.0

  1. sqlite> SELECT total(total_cost)  
  2.   ...> FROM purchase  
  3.   ...> WHERE cate_id='CD001';  
  4.   total(total_cost)  
  5.   -----------------  
  6.   0.0  
  7.  ---------------

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

invoice_no invoice_dt ord_no ord_date receive_dt book_id book_name pub_lang cate_id receive_qty purch_price total_cost
INV0001 2008-07-15 ORD/08-09/0001 2008-07-06 2008-07-19 BK001 Introduction to Electrodynamics English CA001 15 75.00 1125.00
INV0002 2008-08-25 ORD/08-09/0002 2008-08-09 2008-08-28 BK004 Transfer of Heat and Mass English CA002 8 55.00 440.00
INV0003 2008-09-20 ORD/08-09/0003 2008-09-15 2008-09-23 BK005 Conceptual Physics   CA001 20 20.00 400.00
INV0004 2007-08-30 ORD/07-08/0005 2007-08-22 2007-08-30 BK004 Transfer of Heat and Mass English CA002 15 35.00 525.00
INV0005 2007-07-28 ORD/07-08/0004 2007-06-25 2007-07-30 BK001 Introduction to Electrodynamics English CA001 8 25.00 200.00
INV0006 2007-09-24 ORD/07-08/0007 2007-09-20 2007-09-30 BK003 Guide to Networking Hindi CA003 20 45.00 900.00

 Code

  1. sqlite> SELECT cate_id,  
  2.   ...> SUM(receive_qty*purch_price)  
  3.   ...> FROM purchase  
  4.   ...> GROUP BY cate_id;  
  5.   cate_id     SUM(receive_qty*purch_price)  
  6.   ----------  ----------------------------  
  7.   CA001       1725
    CA002       965  
  8.   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

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT SUM(mysum)  
  2.   ...> FROM(
       ...> 
    SELECT COUNT(*) AS  mysum  
  3.   ...> FROM book_mast  
  4.   ...> WHERE no_page>200) AS bb;  
  5.   SUM(mysum)  
  6.   ----------  
  7.   14  

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

invoice_no invoice_dt ord_no ord_date receive_dt book_id book_name pub_lang cate_id receive_qty purch_price total_cost
INV0001 2008-07-15 ORD/08-09/0001 2008-07-06 2008-07-19 BK001 Introduction to Electrodynamics English CA001 15 75.00 1125.00
INV0002 2008-08-25 ORD/08-09/0002 2008-08-09 2008-08-28 BK004 Transfer of Heat and Mass English CA002 8 55.00 440.00
INV0003 2008-09-20 ORD/08-09/0003 2008-09-15 2008-09-23 BK005 Conceptual Physics   CA001 20 20.00 400.00
INV0004 2007-08-30 ORD/07-08/0005 2007-08-22 2007-08-30 BK004 Transfer of Heat and Mass English CA002 15 35.00 525.00
INV0005 2007-07-28 ORD/07-08/0004 2007-06-25 2007-07-30 BK001 Introduction to Electrodynamics English CA001 8 25.00 200.00
INV0006 2007-09-24 ORD/07-08/0007 2007-09-20 2007-09-30 BK003 Guide to Networking Hindi CA003 20 45.00 900.00

Code

  1. sqlite> SELECT SUM(DISTINCT no_of_branch)  
  2.   ...> FROM publisher;  
  3.   SUM(DISTINCT no_of_branch)  
  4.   --------------------------  
  5.   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

invoice_no invoice_dt ord_no ord_date receive_dt book_id book_name pub_lang cate_id receive_qty purch_price total_cost
INV0001 2008-07-15 ORD/08-09/0001 2008-07-06 2008-07-19 BK001 Introduction to Electrodynamics English CA001 15 75.00 1125.00
INV0002 2008-08-25 ORD/08-09/0002 2008-08-09 2008-08-28 BK004 Transfer of Heat and Mass English CA002 8 55.00 440.00
INV0003 2008-09-20 ORD/08-09/0003 2008-09-15 2008-09-23 BK005 Conceptual Physics   CA001 20 20.00 400.00
INV0004 2007-08-30 ORD/07-08/0005 2007-08-22 2007-08-30 BK004 Transfer of Heat and Mass English CA002 15 35.00 525.00
INV0005 2007-07-28 ORD/07-08/0004 2007-06-25 2007-07-30 BK001 Introduction to Electrodynamics English CA001 8 25.00 200.00
INV0006 2007-09-24 ORD/07-08/0007 2007-09-20 2007-09-30 BK003 Guide to Networking Hindi CA003 20 45.00 900.00
  1. sqlite> SELECT cate_id,SUM(total_cost)  
  2.   ...> FROM purchase  
  3.   ...> GROUP BY cate_id;  
  4.   cate_id     SUM(total_cost)  
  5.   ----------  ---------------  
  6.   CA001       1725  
  7.   CA002       965  
  8.   CA003       900  
  9.