SQLite SUM() Function

  Total Views : 681
  Comments : 0
  Keywords : SQLite sum() function to get total of column values with example, SQLite sum() aggregate function to sum values of an expression with example, SQLite get sum of values using sum() function

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 sum of defined expression or column values with examples.

SQLite SUM() Function

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 table. In case if table column contains only NULL values means SUM() function will return NULL.

Syntax of SQLite SUM() Function

Following is the syntax of SQLite SUM() function to get the sum of values in a defined expression.

 

SELECT SUM(ExpressionFROM tablename

[WHERE condition]

[GROUP BY Expression];

In 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 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 sum of column values based on particular Expression then we need to use Group By. It’s an optional.

Example of SQLite SUM() Function

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 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 following query to see the data of dept_master table.

 

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

 

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

 

sqliteSELECT SUM(salaryFROM emp_master;

 

sum(salary)

-----------

178400

SQLite SUM() Function with Group By Clause

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

 

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

SQLite SUM() Function with Order By clause

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.

 

sqliteSELECT 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.salaryDESC;

 

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.

SQLite SUM() Function with Having Clause

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

 

sqliteSELECT 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 sum of salary for each department whose name contains character “l”.

 

This is how we can use SQLite SUM() to calculate sum of defined expression or columns based on our requirements. 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User