Definition on SQLite sum function
SQLite provides the different kinds of aggregate functions to the users, in which that SQLite sum () function is one of the functions that are used to return the sum of all non-null values from the group. If the column contains null values then SQLite returns the null result. SQLite sum () function is work similar to the MySQL sum () function. The non-standard total () function provides an efficient way to work with the different kinds of design in SQL language. The one more important thing about the SQLite total () function is that it always returns the floating result.
Syntax:
Select sum ([all | distinct] specified expression) from specified table name [where specified condition] [group by specified expression];
Explanation:
In the above syntax, we use sum () function with different parameters as follows.
all: Normally sum () function uses all clauses that are present in SQLite, that means sum () function performs the calculation whether values are duplicate values or not.
distinct: Sometimes we need to calculate the sum of unique values then we can use a distinct keyword in the SQL statement as per requirement and it is an optional part of syntax.
specified expression: Specified expression means that SQL statement.
specified table name: This actual table name that we need to fetch records from.
where specified condition: We can provide any specified condition to calculate sum of columns as per user requirement and it is an optional part of this syntax.
group by specified expression: Sometimes we need to calculate the sum of columns on their group so that we can use group by clause as per user requirement and it is an optional part of this syntax.
4.5 (6,830 ratings)
View Course
How sum function work in SQLite?
Now let’s see how sum () function works in SQLite as follows.
The output of the SUM () function is an integer number if all info non-NULL values are integer numbers. In the event that any info value is neither a number nor a NULL value, the consequence of the SUM () function is floating point value. The outcome of the SUM () functions is NULL if and just if all info values are NULL. In the event that there is an integer number flood mistake occurs and all info values are NULL or integer, the SUM () function shows an overflow exception message.
The total () and sum () functions return the sum of all non-NULL values from the group. On the off chance that there are no non-NULL info rows, at that time sum () function returns NULL however total () function returns 0.0. Invalid isn’t ordinarily a supportive outcome for the amount of no lines except for the SQL standard requires it and most other SQL information base motors carry out total() that way so SQLite does it similarly to be viable. The non-standard total () function is given as an advantageous method to work around this plan issue in the SQL language. The outcome of total () function is consistently a floating-point value. The outcome of the sum () function is an integer value if all non-NULL sources of info are integer numbers. In the event that any contribution to sum () function is either a number or a NULL total (,) function returns a floating-point value which may be estimation to the genuine total.
Examples
Now let’s see the different examples of sum () function as follows.
First, we need to create a table, so create a table by using the following statement as follows.
create table emp (emp_id integer primary key, emp_name text not null, emp_email text not null, emp_dept text not null, emp_salary float not null);
Explanation
In above example, we created new table name as emp different attribute such as emp_id with integer data type and primary key, emp_name with text data type and not null, emp_email with text data type and not null and emp_salary with float data type and not null constraint and emp_dept with text data type and not null constraint. End output of the above statement as shown in below screenshot as follows.
.table
Now insert some records into the emp table by using insert into statement as follows.
insert into emp (emp_id, emp_name, emp_email, emp_dept, emp_salary) values (1, “Jay”, “jay@gmail.com”, “COMP”, 15000), (2, “Johan”, “jon@gamil.com”, “IT”, 12000), (3, “Jenny”,”jenny@gmail.com”, “COMP”, 20000), (4, “Sameer”, “sam@gmail.com”, “MECH”, 25000), (5, “Pooja”, “pooja@gamil.com”, “IT”, 19000), (6, “Mark”, “mark@gmail.com”, “MECH”, 24000);
select * from emp;
Explanation
With the help of the above statement, we inserted some records into the stud table successfully. End output of above Statement as shown in below screenshot as follows.
Now perform sum () function as follows.
select sum (emp_salary) from emp;
Explanation
In the above example, we use sum () function, in this example, we need to calculate the total salary of emp at that we can use the above statement. End output of above Statement as shown in below screenshot as follows.
Now let’s see how we can use the sum () function with the group by clause as follows.
select emp_dept, sum(emp_salary) from emp group by emp_dept;
Explanation
In the above example, we use sum () function with group by clause as shown in above statement. Suppose we need to calculate sum emp_salary in department wise at that time we can use the above statement. End output of above Statement as shown in below screenshot as follows.
Now let’s see how we can use the where with sum () function as follows.
select emp_salary sum(emp_salary) from emp where emp_dept = “COMP”;
Explanation
In the above example, we use sum () function with where clause as shown in above statement. Suppose we need to find out the total salary of the COMP department at that time we can use the above statement. End output of above Statement as shown in below screenshot as follows.
Similarly, we can use sum () function with join and having as per user requirement.
Conclusion
We hope from this article you have understood about the SQLite sum function. From the above article, we have learned the basic syntax sum function and we also see different examples of sum function. From this article, we learned how and when we use the SQLite sum function.
Recommended Articles
This is a guide to SQLite sum. Here we discuss the Introduction, syntax, how sum () function works in SQLite, and examples. You may also have a look at the following articles to learn more –
