MySQL SUM

MySQL SUM() Function

Introduction

The SUM() function in MySQL is an aggregate function used to calculate the total sum of a column's numeric values. It is commonly used with GROUP BY to summarize data.

Syntax

SELECT SUM(column_name) FROM table_name;

Parameters:

• column_name → The numeric column whose values need to be summed.
• table_name → The table containing the column.

Basic Example

Step 1: Create a Sample Table

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100),
    amount DECIMAL(10,2)
);

Step 2: Insert Sample Data

INSERT INTO orders (customer_name, amount) VALUES
('Alice', 120.50),
('Bob', 200.00),
('Charlie', 80.75),
('Alice', 150.25);

Step 3: Calculate Total Sales

SELECT SUM(amount) AS total_sales FROM orders;

Output:

+-------------+
| total_sales |
+-------------+
| 551.50      |
+-------------+

Using SUM() with GROUP BY

Calculate Total Sales Per Customer

SELECT customer_name, SUM(amount) AS total_spent 
FROM orders 
GROUP BY customer_name;

Output:

+--------------+-------------+
| customer_name| total_spent |
+--------------+-------------+
| Alice        | 270.75      |
| Bob          | 200.00      |
| Charlie      | 80.75       |
+--------------+-------------+

Using SUM() with WHERE Clause

Calculate Total Sales Greater Than $100

SELECT SUM(amount) AS total_sales 
FROM orders 
WHERE amount > 100;

Output:

+-------------+
| total_sales |
+-------------+
| 470.75      |
+-------------+

Using SUM() with HAVING Clause

Find Customers Who Spent More Than $200

SELECT customer_name, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_name
HAVING total_spent > 200;

Output:

+--------------+-------------+
| customer_name| total_spent |
+--------------+-------------+
| Alice        | 270.75      |
+--------------+-------------+

Using SUM() with Multiple Columns

If you need to sum multiple numeric columns:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    quantity INT
);

INSERT INTO products (product_name, price, quantity) VALUES
('Laptop', 1000.00, 2),
('Phone', 500.00, 5),
('Tablet', 300.00, 3);

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

Output:

+---------------+
| total_revenue |
+---------------+
| 4100.00       |
+---------------+

Conclusion

• SUM() calculates the total of numeric values.
• Works with GROUP BY, HAVING, and WHERE.
• Can sum multiple columns using expressions.

Would you like more advanced queries using SUM()? 🚀