Here we will learn what is SQLite SUM() function and how to use SQLite SUM() function with WHERE, GROUP BY, ORDER BY, Having to calculate the sum of defined expression or column values with examples.
In SQLite SUM() Function is an aggregate function which is used to calculate the sum of values in a specified expression or column.
Generally, in SQLite SUM() function will work with non-NULL numeric values to return the summed values of a column in a table. In case if the table column contains only NULL values means SUM() function will return NULL.
Following is the syntax of the SQLite SUM() function to get the sum of values in a defined expression.
SELECT SUM(Expression) FROM tablename
[WHERE condition]
[GROUP BY Expression];In the above SQLite SUM() function syntax, we defined multiple parameters those are
Expression – Its column or expression which we used to calculate the sum of values in defined column or expression.
Tablename – Its name of the table which we wish to retrieve records from.
[WHERE Condition] – If we have any specific conditions to calculate the sum of column values then we need to specify WHERE based on our requirements. It’s optional.
[Group By Expression] – If we want to calculate the sum of column values based on particular Expression then we need to use Group By. It’s optional.
Now we will see how to use SUM() function in SQLite to calculate the sum of expression values with examples for that we need to create two tables called dept_master and emp_master using the following query.
To create and insert some data in dept_master table execute following query.
CREATE TABLE dept_master
(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_name TEXT);
INSERT INTO dept_master(dept_name)
VALUES('Admin'),
('Sales'),
('Quality Control'),
('Marketing');
Once we create and insert data execute the following query to see the data of the dept_master table.
sqlite> SELECT * FROM dep_master;
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Control
4 Marketing
Same way execute following queries to create and insert some data in emp_master table.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (1,'Honey','Patel', 10100,1),
(2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 50000,3),
(9,'Shwets','Jariwala',19400,2);
Now run the following query to check the records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
9 Shwets Jariwala 19400 2
Now write the query like as shown following to get the sum of salary from emp_master table.
sqlite> SELECT SUM(salary) FROM emp_master;
sum(salary)
-----------
178400
In SQLite, by using Group By clause with SQLite SUM function we can calculate the sum of column values based on the defined expression or column.
Following is the SQLite query to use SQLite SUM() function with Group By clause to calculate the sum of salary based on the department.
sqlite> SELECT d.dept_name,SUM(e.salary)
FROM emp_master e, dept_master d
WHERE e.dept_id=d.dept_id
GROUP BY e.dept_id;
dept_name sum(e.salary)
--------------- -------------
Admin 23100
Sales 58200
Quality Control 97100
If you observe above result we calculated sum of salary for each department.
In SQLite by using Order By clause with SQLite SUM function we can calculate the sum of column values based on particular expression or column and arrange the result set either in ascending or descending order based on our requirements.
Following is the SQLite query to use SQLite SUM() function with Order By clause to calculate the sum of salary based on department and arrange the records in descending order.
sqlite> SELECT d.dept_name,SUM(e.salary)
FROM emp_master e, dept_master d
WHERE e.dept_id = d.dept_id
GROUP BY e.dept_id
ORDER BY SUM(e.salary) DESC;
dept_name sum(e.salary)
--------------- -------------
Quality Control 97100
Sales 58200
Admin 23100
The above example will calculate the sum of salary for each department and those records will arrange in descending order based on salary.
In SQLite by using Having clause with SQLite SUM function we can calculate the sum of column values based on the particular expression or column.
Following is the SQLite query to use SQLite SUM() function with Having clause to calculate the sum of salary based on the department whose name contains “l”.
sqlite> SELECT d.dept_name,SUM(e.salary)
FROM emp_master e, dept_master d
WHERE e.dept_id = d.dept_id
GROUP BY e.dept_id
HAVING d.dept_name LIKE '%l%';
dept_name sum(e.salary)
---------- -------------
Sales 58200
Quality Control 97100
The above example returns the sum of salary for each department whose name contains character “l”.
This is how we can use SQLite SUM() to calculate the sum of defined expression or columns based on our requirements.