SQLite SUM() Function

SQLite is an embedded database that doesn’t use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. The main features of SQLite are that it is a tiny, quick, self-contained, reliable, full-featured SQL database engine.

In this article, we will understand the SUM Function in SQLite in detail along with some practical examples and so on.

SQLite SUM Function

The SUM function in SQLite is an Aggregate Function used to find the sum of all non-NULL values in a column. The output of the SUM function is mostly an integer if all the non-null values present in that column are integers. If the values are mixed (Integer and Non-Integers) then the result will be in float. If the SUM function is used on a column that consists of only NULL values or has only one row that is NULL / Empty, it will not throw any error, but it will return NULL as output, not 0.

Examples of SQLite SUM Function

To understand the SUM function we will use some table to perform various operations and queries. Here we have the employees table which consists of empID, DeptID, FirstName, LastName, Salary, and Location as Columns. After inserting some data into the table.

Output:

Syntax:

SUM( [ALL | DISTINCT] <Column_Name>)

Explanation of Syntax:

By default, SUM considers every value present in a particular column passed as it’s argument and returns their sum. It is a type of Aggregate function which is basically used to calculate the sum of every non-NULL values present in a certain column. By default the SQLite SUM Function uses ALL clause, means that calculate the sum of every value present in that column, regardless of they are duplicate or not, there is no need to use the word ALL to tell SUM to find the sum of every value, it does it by default. But, there is another way to calculate the SUM of all the DISTINCT values of a column using the DISTINCT keyword. We will see how to use both of them in coming examples.

Example 1: Simple SUM Function

We will use the SUM Function Normally to find out the sum of ALL the values of the salary column. Write the below command in the command line –

Query:

SELECT SUM(Salary)
 AS Total_Salary 
FROM Employees;

Output:

Explanation:

Here an Alias “Total_Salary” has been used to give the title “Total_Salary” to the column which will hold the result of the SUM function, SUM will work even without this. Here simply we are using the SUM function by passing the Salary column inside the paranthesis and we are renaming the resultant column as Total_Salary and fetching ALL the values from the Salary column of the table Employees.

Example 2: SUM Function Using DISTINCT Keyword

As we can see in the syntax, we have to explicitly mention the word DISTINCT to signify that we want the DISTINCT sum not the SUM of all values present in the column. We will now find the sum of only the unique salaries present in the column.

Query:

SELECT SUM (DISTINCT Salary)
 AS Total_Distinct_Salary
 FROM Employees;

Output:

Explnation: As we can see, we got a different output we have only the unique values were added. The heading of the column is also changed to the Alias passed which is Total_Distinct_Salary.

Example 3: SUM Function Using INNER JOIN Clause

To demonstrate the purpose of INNER JOIN clause alongside SUM function, we need to first create another table which has some common column names in between them. For this purpose, we will be using a table called Employee_Details which consists of other details of the employees, such as their position and their Years of Experience (YOE). The common things between both the tables are empID, FirstName and LastName and Dept_ID.

Write the below command to create the Employee_Details Table –

CREATE TABLE Employee_Details
 (
empID INTEGER,
 Dept_ID TEXT,
 FirstName TEXT,
 LastName TEXT,
 Position TEXT,
 YOE INTEGER
);

Now populate the table using INSERT INTO statements –

INSERT INTO Employee_Details VALUES(1, 'D1', 'Sonia', 'Wong', 'ASE', 0);
INSERT INTO Employee_Details VALUES(2, 'D1', 'Neel', 'Lee', 'ASE', 0);
INSERT INTO Employee_Details VALUES(3, 'D2', 'Melody', 'Abott', 'SE',1);
INSERT INTO Employee_Details VALUES(4, 'D3', 'Trinity', 'Kirk', 'ANL',1);
INSERT INTO Employee_Details VALUES(5, 'D1', 'Miley', 'Webster', 'Sr ANL',3);
INSERT INTO Employee_Details VALUES(6, 'D2', 'Sydnee', 'Donaldson', 'CONS',2);
INSERT INTO Employee_Details VALUES(7, 'D1', 'Matilda', 'Roach', 'Sr CONS',4);
INSERT INTO Employee_Details VALUES(8, 'D2', 'Chanel', 'Mcneil', 'ASE',0);
INSERT INTO Employee_Details VALUES(9, 'D1', 'Gilberto', 'Blake', 'CM',2);
INSERT INTO Employee_Details VALUES(10, 'D2', 'Harmony', 'Serrano', 'PU',5);
INSERT INTO Employee_Details VALUES(11, 'D3', 'Simon', 'Riley', 'TR',2);
INSERT INTO Employee_Details VALUES(12, 'D1', 'John', 'McTavish', 'CS',1);
INSERT INTO Employee_Details VALUES(13, 'D2', 'John', 'Price', 'M&I',3);
INSERT INTO Employee_Details VALUES(14, 'D1', 'Yuri', 'Makarov',NULL,NULL);
INSERT INTO Employee_Details VALUES(15, 'D3', 'Nicholas', 'Rogers',NULL,NULL);

Now we will use the SUM function along with the INNER JOIN clause to print the total salary of each department using the SUM function and GROUP BY clause.

Query:

SELECT 
Dept_ID, SUM(Salary) 
FROM Employees 
INNER JOIN
Employee_Details ON Employee_Details.empID = Employees.empID
 GROUP BY Dept_ID;

Output:

Explanation: Here, the GROUP BY clause is also used with the INNER JOIN and SUM function, because if it was not used then all the values (Salary) irrespective of department would have been added together and printed with a random Department ID. Our goal is to get the Department wise Total Salaries, this is why we are using the GROUP BY clause with them.

Example 4: SUM Function with HAVING Clause

In this section, We will see how we can use the HAVING clause with the SUM function. To use the HAVING Clause, we must have to use the GROUP BY Aggregate Function before it. As we saw in the previous example where we printed the Total Salary Department wise, we will modify that output and print only those total salaries which are greater than 100000.

Syntax:

 SELECT DeptID,
 SUM(Salary)
 FROM Employees
 GROUP BY DeptID
 HAVING SUM(Salary) > 100000;

Output:

Explanation: In the above Query we calculate the sum of all salaries and then group them together by the DeptID and then sort out the final results based on the condition Total Salary is greater than 100000.

Conclusion

In this article, we saw how the SUM function of SQLite can be used for various purpose, from finding the total sum of integers to Joining different tables together and sorting out results using the HAVING and GROUP BY clause. The SUM Aggregate function is a very useful function when it comes to Arithmetical Tasks and sorting out based on arithmetical conditions.

SUM function is also helpful for several other purposes, like it is helpful for statistical analysis, it provides a sum measure of quantitative data. Calculation of the sum can also be based on certain conditions provided. SUM function is also efficient in finding out the aggregated SUM without the need of any kind of manual calculation.