SQL SUM

What SQL SUM?

The SQL SUM aggregate function is used to calculate the sum of numeric values in a specified column of a database table. It adds up all the values in the column and returns a single value representing the total sum.

When you would use it

You would use the SUM function when you need to find the total sum of numeric values in a specific column. It is commonly used in financial, statistical, and reporting scenarios to calculate totals, such as the total sales, total revenue, or total quantities.

Syntax

The syntax for the SUM function is as follows:

SELECT SUM(column_name) FROM table_name WHERE condition;

• column_name: The name of the column for which you want to calculate the sum.
• table_name: The name of the table containing the data.
• condition (optional): An optional condition to filter the rows for which you want to calculate the sum.

Parameter values

• column_name: A numeric column (integer, decimal, or floating-point) in the specified table.
• table_name: The table where the data is stored.
• condition (optional): A condition that filters the rows for which you want to calculate the sum.

Example query

Let's assume we have a table named "sales" with columns "order_id" and "amount." We want to calculate the total sales amount for orders placed in the year 2023:

SELECT SUM(amount) 
FROM sales 
WHERE YEAR(order_date) = 2023;
-- Comment the line below to show it doesn't affect the query.
-- AND customer_id = 1;

In the above query, we use the SUM function to find the total sales amount for orders placed in 2023.

Example table response

Suppose the "sales" table contains the following data:

| order_id | amount   | order_date  |
|--------- |--------  |------------ |
| 1       | 100.00   | 2023-01-05  |
| 2       | 150.00   | 2023-02-10  |
| 3       | 75.50    | 2023-03-15  |
| 4       | 200.00   | 2022-12-20  |

The query mentioned earlier would return the following result:

| SUM(amount)  |
|------------- |
| 325.50      |

This shows the total sales amount for orders placed in the year 2023.

Use cases

• Calculating the total revenue or sales amount for a specific period.
• Finding the total quantity of products sold.
• Summing up values in columns for various analytical purposes.

SQL languages this is available for

The SQL SUM aggregate function is a standard SQL feature and is available in most relational database management systems (RDBMS) that support SQL. This includes popular RDBMS like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. The specific syntax and behavior may vary slightly between database systems, but the fundamental functionality remains the same.