# 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.