MySql - Summing values

Summing Values (SUM) in MySQL

Introduction to SUM in MySQL

The SUM function in MySQL is an aggregate function used to calculate the total sum of a numeric column. This function is frequently used in data analysis, reporting, and summarization tasks where numerical totals are required. Whether calculating total sales, total expenses, or aggregating metrics like distances or counts, the SUM function is indispensable.

In this guide, we will explore the syntax, practical examples, advanced usages, and performance considerations related to the SUM function in MySQL.

Basic Syntax of SUM

SELECT SUM(column_name) AS total_sum
FROM table_name
WHERE condition;

Here:

• column_name: The numeric column whose values you want to add together.
• total_sum: An alias for the resulting summed value.
• condition: Optional filtering criteria to specify which rows should be included in the calculation.

Data Setup for Examples

We will use a sales table to illustrate how the SUM function works:

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    quantity INT,
    price DECIMAL(10, 2),
    sale_date DATE
);

INSERT INTO sales (product_name, category, quantity, price, sale_date) VALUES
('Laptop', 'Electronics', 2, 700.00, '2024-06-01'),
('Smartphone', 'Electronics', 3, 300.00, '2024-06-02'),
('Tablet', 'Electronics', 5, 200.00, '2024-06-05'),
('Headphones', 'Accessories', 4, 50.00, '2024-06-03'),
('Charger', 'Accessories', 6, 20.00, '2024-06-04'),
('Mouse', 'Accessories', 10, 15.00, '2024-06-06');

Summing a Column

Summing Total Quantity Sold

SELECT SUM(quantity) AS total_quantity
FROM sales;

Result

+----------------+
| total_quantity |
+----------------+
| 30             |
+----------------+

This query sums all the quantities from the sales table, giving us the total number of items sold.

Summing with Conditions

Example: Total Sales in Electronics Category

SELECT SUM(quantity) AS electronics_quantity
FROM sales
WHERE category = 'Electronics';

Result

+----------------------+
| electronics_quantity |
+----------------------+
| 10                   |
+----------------------+

This example sums the quantity of only the Electronics category.

Example: Total Revenue

Total revenue can be calculated by multiplying quantity by price for each sale and summing the result.

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

Result

+---------------+
| total_revenue |
+---------------+
| 3950.00       |
+---------------+

SUM with GROUP BY Clause

To get a summed value grouped by another column, use the GROUP BY clause.

Example: Total Quantity per Category

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

Result

+-------------+----------------+
| category    | total_quantity |
+-------------+----------------+
| Accessories | 20             |
| Electronics | 10             |
+-------------+----------------+

Example: Total Revenue per Product

SELECT product_name, SUM(quantity * price) AS product_revenue
FROM sales
GROUP BY product_name;

Result

+-------------+----------------+
| product_name| product_revenue|
+-------------+----------------+
| Laptop      | 1400.00        |
| Smartphone  | 900.00         |
| Tablet      | 1000.00        |
| Headphones  | 200.00         |
| Charger     | 120.00         |
| Mouse       | 150.00         |
+-------------+----------------+

SUM with JOIN Operations

In complex databases, SUM is often used with JOINs to aggregate data across multiple tables.

Example Setup: Products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50)
);

INSERT INTO products (product_id, product_name, category) VALUES
(1, 'Laptop', 'Electronics'),
(2, 'Smartphone', 'Electronics'),
(3, 'Tablet', 'Electronics'),
(4, 'Headphones', 'Accessories'),
(5, 'Charger', 'Accessories'),
(6, 'Mouse', 'Accessories');

Join Query to Get Total Revenue by Category

SELECT p.category, SUM(s.quantity * s.price) AS total_revenue
FROM sales s
JOIN products p ON s.product_name = p.product_name
GROUP BY p.category;

Result

+-------------+---------------+
| category    | total_revenue |
+-------------+---------------+
| Accessories | 470.00        |
| Electronics | 3480.00       |
+-------------+---------------+

Using SUM with DISTINCT

To sum distinct values, use SUM(DISTINCT column_name).

Example: Summing Distinct Quantities

SELECT SUM(DISTINCT quantity) AS sum_distinct_quantity
FROM sales;

Result

+-----------------------+
| sum_distinct_quantity |
+-----------------------+
| 27                    |
+-----------------------+

SUM with HAVING Clause

The HAVING clause filters aggregated data.

Example: Categories with Revenue Above 1000

SELECT category, SUM(quantity * price) AS total_revenue
FROM sales
GROUP BY category
HAVING total_revenue > 1000;

Result

+-------------+---------------+
| category    | total_revenue |
+-------------+---------------+
| Electronics | 3480.00       |
+-------------+---------------+

SUM in Subqueries

Example: Select Products Exceeding Average Revenue

SELECT product_name, SUM(quantity * price) AS product_revenue
FROM sales
GROUP BY product_name
HAVING product_revenue > (
    SELECT AVG(quantity * price) FROM sales
);

Result

+-------------+----------------+
| product_name| product_revenue|
+-------------+----------------+
| Laptop      | 1400.00        |
| Smartphone  | 900.00         |
| Tablet      | 1000.00        |
+-------------+----------------+

Handling NULL Values in SUM

SUM automatically ignores NULLs. For example, if a price is NULL, that row is not counted in the sum.

Example

INSERT INTO sales (product_name, category, quantity, price, sale_date)
VALUES ('Keyboard', 'Accessories', 5, NULL, '2024-06-07');

SELECT SUM(price) AS total_price
FROM sales;

The price for 'Keyboard' being NULL is excluded from the total sum.

SUM with Window Functions (MySQL 8+)

Window functions allow cumulative sums over partitions of data.

Example: Running Total of Revenue

SELECT 
    product_name,
    quantity * price AS revenue,
    SUM(quantity * price) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Result

+-------------+---------+--------------+
| product_name| revenue | running_total|
+-------------+---------+--------------+
| Laptop      | 1400.00 | 1400.00       |
| Smartphone  | 900.00  | 2300.00       |
| Headphones  | 200.00  | 2500.00       |
| Charger     | 120.00  | 2620.00       |
| Tablet      | 1000.00 | 3620.00       |
| Mouse       | 150.00  | 3770.00       |
| Keyboard    | NULL    | 3770.00       |
+-------------+---------+--------------+

The SUM function in MySQL is a core aggregate function that plays a vital role in data summarization and analysis. Whether you’re calculating simple totals, aggregating with GROUP BY, or analyzing data over time with window functions, SUM is a powerful and versatile tool.

By understanding its syntax, behavior with NULLs, integration with GROUP BY, JOINs, HAVING, and window functions, users can leverage SUM effectively to derive insights from their datasets. With good indexing and query optimization, even complex SUM queries can be made efficient and scalable.