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.