How to Use the SUM() Function in MySQL

08/04/2021

Contents

In this article, you will learn how to use the SUM() function in MySQL.

Using the SUM() function in MySQL

The SUM() function in MySQL is used to calculate the sum of all values in a particular column of a table.

Syntax

Here’s the basic syntax of the SUM() function in MySQL:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

In this example, “column_name” is the name of the column we want to sum, “table_name” is the name of the table, and “condition” is the criterion that must be met in order for a row to be included in the sum. If we want to sum all rows in the table, we can simply omit the “WHERE” clause.

Example of using SUM()

Let’s look at an example to see how we can use the SUM() function in MySQL. Suppose we have a table called “sales” with the following data:

+----+------------+-------+
| id | date       | amount|
+----+------------+-------+
| 1  | 2022-01-01 | 1000  |
| 2  | 2022-01-02 | 2000  |
| 3  | 2022-01-03 | 3000  |
| 4  | 2022-01-04 | 4000  |
| 5  | 2022-01-05 | 5000  |
+----+------------+-------+

We can use the SUM() function to calculate the total amount of sales as follows:

SELECT SUM(amount)
FROM sales;

The result will be:

+------------+
| SUM(amount) |
+------------+
| 15000      |
+------------+

We can also use the SUM() function to calculate the total amount of sales within a specific date range. For example, if we want to calculate the total amount of sales between January 2, 2022 and January 4, 2022, we can use the following query:

SELECT SUM(amount)
FROM sales
WHERE date >= '2022-01-02' AND date <= '2022-01-04';

The result will be:

+------------+
| SUM(amount) |
+------------+
| 9000       |
+------------+

In this example, the SUM() function will calculate the sum of the "amount" column for all rows that meet the specified date range condition.

Using SUM() with GROUP BY

We can also use the SUM() function with GROUP BY to calculate the sum of a particular column for each group in the table. Here's the syntax:

SELECT column_name, SUM(amount)
FROM table_name
GROUP BY column_name;

In this example, the SUM() function will calculate the sum of the "amount" column for each unique value in the "column_name" column.

For example, if we want to calculate the total sales for each date in the "sales" table, we can use the following query:

SELECT date, SUM(amount)
FROM sales
GROUP BY date;

The result will be:

+------------+------------+
| date       | SUM(amount) |
+------------+------------+
| 2022-01-01 | 1000       |
| 2022-01-02 | 2000       |
| 2022-01-03 | 3000       |
| 2022-01-04 | 4000       |
| 2022-01-05 | 5000       |
+------------+------------+

In this example, the SUM() function is used with GROUP BY to calculate the total amount of sales for each date in the "sales" table.

Using SUM() with HAVING

We can also use the SUM() function with HAVING to filter the results based on the sum of a particular column. Here's the syntax:

SELECT column_name, SUM(amount)
FROM table_name
GROUP BY column_name
HAVING condition;

In this example, the HAVING clause is used to filter the results based on the sum of the "amount" column for each unique value in the "column_name" column.

For example, if we want to find the dates with total sales greater than 3000 in the "sales" table, we can use the following query:

SELECT date, SUM(amount)
FROM sales
GROUP BY date
HAVING SUM(amount) > 3000;

The result will be:

+------------+------------+
| date       | SUM(amount) |
+------------+------------+
| 2022-01-04 | 4000       |
| 2022-01-05 | 5000       |
+------------+------------+

In this example, the SUM() function is used with HAVING to filter the results to only show dates with total sales greater than 3000.

Using SUM() with DISTINCT

We can also use the SUM() function with DISTINCT to calculate the sum of distinct values in a column. Here's the syntax:

SELECT SUM(DISTINCT column_name)
FROM table_name;

In this example, the SUM() function will calculate the sum of distinct values in the "column_name" column.

For example, if we want to calculate the total number of unique sales representatives in the "sales" table, we can use the following query:

SELECT SUM(DISTINCT sales_rep)
FROM sales;

The result will be:

+-----------------------+
| SUM(DISTINCT sales_rep) |
+-----------------------+
| 3                     |
+-----------------------+

In this example, the SUM() function is used with DISTINCT to calculate the total number of unique sales representatives in the "sales" table.

#MySQL