# How to Use the SUM() Function in MySQL

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.