How the SUM() function works in Mariadb?

The SUM() function in MariaDB is an aggregate function used to calculate the sum of values in a specified column or expression. It is typically used in conjunction with the GROUP BY clause to calculate the sum of values for each group. The SUM() function is particularly useful when you need to perform calculations on numerical data or when you want to find the total of a set of values.

Syntax

The syntax for the MariaDB SUM() function is as follows:

SUM([DISTINCT | ALL] expression)

• DISTINCT (optional): This keyword is used to calculate the sum of distinct (unique) values in the specified expression. If DISTINCT is not used, the function considers all values.
• ALL (optional): This keyword is used to calculate the sum of all values in the specified expression. If neither DISTINCT nor ALL is specified, the function considers all values by default.
• expression: The column or expression for which you want to calculate the sum. This is a required parameter.

The function returns the sum of the specified expression. If the expression contains NULL values, they are ignored in the calculation.

Examples

Example 1: Calculating the sum of a column

This example demonstrates how to calculate the sum of values in a column.

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (product VARCHAR(50), quantity INT, price DECIMAL(10,2));
INSERT INTO sales (product, quantity, price) VALUES
    ('Product A', 10, 9.99),
    ('Product B', 5, 14.99),
    ('Product A', 8, 9.99),
    ('Product C', 12, 19.99);

SELECT SUM(quantity) AS total_quantity
FROM sales;

The following is the output:

+----------------+
| total_quantity |
+----------------+
|             35 |
+----------------+

In this example, the SUM() function calculates the sum of the quantity column in the sales table, resulting in 35.

Example 2: Calculating the sum of an expression

The SUM() function can calculate the sum of an expression instead of a single column.

SELECT SUM(quantity * price) AS total_revenue
FROM sales;

The following is the output:

+---------------+
| total_revenue |
+---------------+
|        494.65 |
+---------------+

In this example, the SUM() function calculates the sum of the product of quantity and price for each row in the sales table, resulting in the total revenue of 494.65.

Example 3: Using SUM() with GROUP BY

The SUM() function is often used in combination with the GROUP BY clause to calculate the sum of values for each group.

SELECT product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product;

The following is the output:

+-----------+----------------+
| product   | total_quantity |
+-----------+----------------+
| Product A |             18 |
| Product B |              5 |
| Product C |             12 |
+-----------+----------------+

In this example, the SUM() function calculates the sum of the quantity column for each distinct product value, grouping the results by the product column.

Example 4: Using DISTINCT with SUM()

The DISTINCT keyword can be used with SUM() to calculate the sum of distinct (unique) values in the specified expression.

DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (value INT);
INSERT INTO numbers (value) VALUES (1), (2), (2), (3), (3), (3), (4);

SELECT SUM(DISTINCT value) AS sum_distinct
FROM numbers;

The following is the output:

+--------------+
| sum_distinct |
+--------------+
|           10 |
+--------------+

In this example, the SUM(DISTINCT value) calculates the sum of distinct (unique) values in the value column, resulting in 10 (1 + 2 + 3 + 4).

Example 5: Handling NULL values

The SUM() function ignores NULL values in the specified expression.

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (product VARCHAR(50), quantity INT, price DECIMAL(10,2));
INSERT INTO sales (product, quantity, price) VALUES
    ('Product A', 10, 9.99),
    ('Product B', 5, NULL),
    ('Product A', 8, 9.99),
    ('Product C', 12, 19.99);

SELECT SUM(quantity * price) AS total_revenue
FROM sales;

The following is the output:

+---------------+
| total_revenue |
+---------------+
|        419.70 |
+---------------+

In this example, the row with a NULL value for the price column is ignored when calculating the sum of the quantity * price expression.

Related Functions

The following are some functions related to the MariaDB SUM() function:

• MariaDB AVG() function is used to calculate the average of values in a specified column or expression.
• MariaDB COUNT() function is used to count the number of rows or non-NULL values in a specified column or expression.
• MariaDB MAX() function is used to find the maximum value in a specified column or expression.
• MariaDB MIN() function is used to find the minimum value in a specified column or expression.

Conclusion

The SUM() function in MariaDB is a powerful tool for calculating the sum of values in a specified column or expression. It can be used with or without the GROUP BY clause, and supports the DISTINCT keyword to calculate the sum of unique values. By understanding the syntax and usage of this function, you can perform various calculations and aggregations on numerical data in your SQL queries and applications.