SQLite | SQLite Functions | Calculate Column Totals with sum and total

Tags:

• SQLite
• Database

The sum() and total() functions calculate the sum of values in a column.

Using sum and total

Use sum() to calculate a total. The syntax is as follows.

sum(column_name)

It returns the sum of the specified column. With GROUP BY, it returns a total per group.

The total() function also calculates a sum.

total(column_name)

It likewise supports per-group totals with GROUP BY.

sum() returns an integer when all inputs are integers, whereas total() always returns a floating-point value. Both return floating point if any input is floating point.

If all values are NULL, sum() returns NULL, while total() returns 0.0.

–

Create the following table for an example.

create table store (address text, sales);

sqlite> create table store (address text, sales);
sqlite> 

Insert the following data.

insert into store values ('Seoul', NULL);

sqlite> insert into store values ('Seoul', NULL);
sqlite> 
sqlite> select * from store;
address     sales     
----------  ----------
Seoul                 
sqlite> 

The table currently has one row whose sales value is NULL. Calculate the total with both functions.

select sum(sales), total(sales) from store;

sqlite> .mode column
sqlite> .header on
sqlite> 
sqlite> select sum(sales), total(sales) from store;
sum(sales)  total(sales)
----------  ------------
            0.0         
sqlite> 

With only NULL values, sum() returns NULL and total() returns 0.0.

Add more data.

insert into store values ('Busan', 450);
insert into store values ('Daejeon', 380);
insert into store values ('Seoul', 95);

sqlite> insert into store values ('Busan', 450);
sqlite> insert into store values ('Daejeon', 380);
sqlite> insert into store values ('Seoul', 95);
sqlite> 
sqlite> select * from store;
address     sales     
----------  ----------
Seoul                 
Busan       450       
Daejeon     380       
Seoul       95        
sqlite> 

Calculate the totals again.

select sum(sales), total(sales) from store;

sqlite> select sum(sales), total(sales) from store;
sum(sales)  total(sales)
----------  ------------
925         925.0       
sqlite> 

Since all values are integers, sum() returns an integer while total() returns floating point.

Add a floating-point value.

insert into store values ('Busan', 320.45);

sqlite> insert into store values ('Busan', 320.45);
sqlite> 
sqlite> select * from store;
address     sales     
----------  ----------
Seoul                 
Busan       450       
Daejeon     380       
Seoul       95        
Busan       320.45    
sqlite> 

Calculate the totals again.

select sum(sales), total(sales) from store;

sqlite> select sum(sales), total(sales) from store;
sum(sales)  total(sales)
----------  ------------
1245.45     1245.45     
sqlite> 

When a floating-point value is present, both functions return floating point.

Finally, group by address and calculate totals for each group.

select address, sum(sales), total(sales) from store group by address;

sqlite> select address, sum(sales), total(sales) from store group by address;
address     sum(sales)  total(sales)
----------  ----------  ------------
Busan       770.45      770.45      
Daejeon     380         380.0       
Seoul       95          95.0        
sqlite> 

A separate total is returned for each address.