How To Use SUM Function In MySQL

Introduction

In this article, we will explore the SUM() function in MySQL, a commonly used aggregate function that calculates the sum of all values in a column. We will provide examples of how to use this function in MySQL queries and cover some advanced concepts like using the WHERE clause with the SUM() function.

What is MySQL?

MySQL is an open-source relational database management system (RDBMS) used to store and manage data. It is a widely used database platform that supports various operating systems, programming languages, and applications. MySQL uses the SQL language to manage the data in a relational database.

What is the SUM() function in MySQL?

The SUM() function in MySQL is an aggregate function that calculates the sum of values in a column. This function returns the sum of all values in an expression. And the SUM() functions ignore NULL. 

Syntax

SELECT SUM(Column_name) FROM <table_name>;

Example

1. Creating a DATABASE

​//creating a database in MySQL with the name Fruitsdb
create database FruitsDb;
//using database 
use FruitsDb;

2. Creating Table

​create table Fruits
(FruitsId int not null primary key auto_increment, FruitsName varchar(50), 
FruitsQuantity varchar(50), FruitsCost int);

This query will create a table called Fruits with columns FruitsId, FruitsName, FruitsQuantity, and FruitsCost. The FruitsId column is defined as an integer that cannot be null and will automatically increment for each new record and is designated as the primary key for the table. The FruitsName column is defined as a string that can be null. The FruitsQuantity column is defined as a string that also can be null. The FruitsCost column is defined as an integer that can be null.

3. Inserting values

​insert into Fruits (FruitsName, FruitsQuantity, FruitsCost) values 
("Apple", "1Kg", 100), ("Pineapple", "2Kg", 140), ("Banana", "1dozen", 90), 
("Pomegranate", "2Kg", 70),("Papaya", "1Kg", 40), ("Grapes", "5Kg", 80), 
("Mango", "5Kg", 155), ("Orange", "2Kg", 70), ("Berries", "3Kg", 90), ("Cherry", "2Kg", 85), ("Watermelon", "2Kg", 50);

This inserts eleven new records into the Fruits table with the specified values. 
Note- Insert multiple records at once by separating the sets of values with commas. Each set of values must be enclosed in parentheses and separated by a comma.

4. Retrieve the data

​select * from Fruits;

This query retrieves data from the Fruits table and returns all records for all columns.

5. Using the SUM() function to find the sum of FuitsCost

select FruitsName, sum(FruitsQuantity + FruitsCost) as SumCost 
from Fruits group by FruitsName
order by SumCost desc;

This query shows all records of the FruitsName column and calculates the sum of FruitsQuantity and FruitsCost from the Fruits table, and then the results are sorted based on their condition. Here  

 6. MySQL SUM function with the WHERE clause

This returns the result based on the condition specified in the WHERE clause.

SELECT SUM(FruitsCost) 
FROM fruits where FruitsCost > 90;

This query retrieves data from the Fruits table and calculates the sum of the FruitsCost column for all records where the FruitsCost is greater than 90.

Conclusion

In this article, I have discussed the SUM() Function of Aggregate function in MySQL with examples.
Thanks for reading this article.