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.
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.
Contact Us