MariaDB SUM() Function

MariaDB is an open-source database that comes under the Relational DataBase Management System(RDBMS). It was bought by Oracle in 2009. MariaDB is highly Compatible with MySQL. It offers exceptional performance and scalability which is optimized for the performance and efficient handling of large data volumes.

MariaDB can run on various Operating Systems like Windows, Linux, and MacOS. In this article, we will learn about the SUM Function in detail along with their examples, and perform various queries using various Functions Like GROUP BY, HAVING, and DISTINCT Clause.

MariaDB SUM Function

In MariaDB, the SUM Function is used when we need to sum the values of an expression. The SUM() function in MariaDB is an Aggregate Function that calculates the sum of values in a specified column. The SUM Function takes a set of values and returns the sum, it ignores NULL values as the AVG() Function does.

Syntax:

SELECT SUM(expression)
FROM TABLE
WHERE [CONDITION];

Examples of MariaDB SUM Function

To understand the SUM function better, we need some tables on which we will perform various queries and operations. Here we will create a table called Employees which consist of EMPID, EMPNAME, EMPSALARY as Columns.

Syntax of Creating a Table:

CREATE TABLE Employees
 (   EMPID INT ,
   EMPNAME varchar(30) NOT NULL,
   EMPSALARY INT
 );

Let’s INSERT Some data into the Employees Table are:

INSERT INTO Employees (EMPID, EMPNAME, EMPSALARY) VALUES
(1, 'PRUDHVI', 1000),
(2, 'RAGHU', 2000),
(3, 'HARSHA', 3000),
(4,'vasanth',5000);

Employees Table

Explanation: We have created a table using “CREATE TABLE” statement and named as Employees mentioned the fields we want the data to insert along with its corresponding datatypes.

After creation of table , we have inserted the data into fields of table using INSERT statement and make sure that the datatype of data inserting should be same with datatype mentioned in creation.

Example 1: Using SUM Function

Let’s Calculate the total salary of Employees Table.

Query:

SELECT SUM(EMPSALARY)
FROM Employees;

Output:

Output Using SUM Function

Explanation: SUM Function does arithmetic sum operation , as we have given EMPSALARY field as parameter to it adds all the values present in it and returns as an output.

Example 2: SUM Function Using DISTINCT Clause

Let’s Calculate the total sum of distinct employee salaries from the Employees table.

Query:

SELECT SUM(DISTINCT EMPSALARY) as "Total Salary"
FROM Employees;

Output:

Output Using DISTINCT

Explanation: In the above Query we have used the DISTINCT Clause with the SUM Function. Here DISTINCT Clause with SUM allow the unique values to be added only. It won’t allowed the duplicate values to be added in the Total_Salary Column. If we clearly saw the Employees table then we observe that all the values in EMPSALARY Column have a Unique values that’s why their total sum is 11000.

Example 3: SUM Function Using GROUP BY Clause

In Employees Table which containing employee IDs (EMPID) and their corresponding salaries (EMPSALARY), calculate the total salary earned by each individual employee.

Query:

SELECT SUM(EMPSALARY)
FROM
Employees
GROUP BY EMPID;

Output:

Output for SUM( ) with group by Clause

Explanation: In the Employees Table, Since each employee has only one salary entry, the total salaries match individual salaries. The GROUP BY statement calculates the sum value for each group.i.e EMPID and displays the output. The Output displayed the total sum for each EMPID. Like sum is 1000 for EMPID 1, sum is 2000 for EMPID 2, similarly for all EMPID Fields the sum is displayed.

Example 4: SUM Function Using Having Clause

Let’s find out the employees named ‘vasanth‘ whose total salary exceeds 2000.

Query:

SELECT EMPID, SUM(EMPSALARY) AS Employeesalary
FROM Employees
WHERE EMPNAME = 'vasanth'
GROUP BY EMPID
HAVING SUM(EMPSALARY) > 2000;

Output:

Output after using SUM( ) with Having Clause

Explanation: In the above query, we have added HAVING Clause. The SELECT statement selected the EMPID column from Employees table, The SUM Function will add all values in EMPSALARY and returns its sum. The EMPID 1 belongs to an employee named ‘vasanth‘. and their total salary is 5000, exceeding the 2000, thus the query identifies them as matching both criteria.

Conclusion

Generally we use SUM( ) operator to retrieve addition data from data table. Most of the situations this operator is used in Data Extraction and Data Analysing. It’s highly adaptable, used for basic sums, unique value sums (with DISTINCT), grouped sums (with GROUP BY), and conditional sums (with HAVING).