SQL SUM

The SUM() function in SQL calculates the total (sum) of a numeric column. It is commonly used to aggregate data, such as finding the total sales, revenue, or quantities.

 

Syntax of SQL SUM

SELECT SUM(column_name) FROM table_name WHERE condition;

• SUM(): The aggregate function used to compute the total of numeric values.
• column_name: The name of the column containing the numeric data.
• table_name: The name of the table where the data is stored.
• condition: Optional filter to calculate the sum for specific rows.

 

Key Points

• SUM() works only with numeric data types.
• It ignores NULL values during the calculation.
• The result is a single value representing the sum of the column values.

 

Example of SQL SUM

Consider the following table:

Table: Orders

To calculate the total quantity of all orders:

SELECT SUM(quantity) AS total_quantity FROM orders;

Output:

Explanation:

• The SUM(quantity) function adds up the values in the quantity column: 3 + 5 + 2 + 4 + 6 = 20.

 

SQL SUM with Conditions

You can use the SUM() function with the WHERE clause to calculate the sum of rows that match a specific condition.

Example: Total price of orders with quantity greater than 3:

SELECT SUM(price) AS total_price FROM orders WHERE quantity > 3;

Output:

Explanation:

• The SUM(price) function adds up the price values for rows where quantity > 3 (Mango and Orange): 2.8 + 1.5 = 6.8.

 

SQL SUM with Grouping

The SUM() function can be used with the GROUP BY clause to calculate totals for groups of data.

Example: Total quantity for each product:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name;

Output:

Explanation:

• The SUM(quantity) function calculates the total quantity for each product by grouping rows based on the product_name column.

 

SQL SUM with Joins

You can also use SUM() in queries with joins to calculate totals across related tables.

Example: Calculate total revenue:

SELECT SUM(quantity * price) AS total_revenue FROM orders;

Output:

Explanation:

• The query multiplies the quantity and price for each row and sums the results:
  (3 * 2.5) + (5 * 1.2) + (2 * 3.0) + (4 * 2.8) + (6 * 1.5) = 48.6.

 

Summary

The SUM() function in SQL is a powerful tool for aggregating numeric data. It is versatile and can be combined with conditions, grouping, and joins to perform complex calculations. The function ignores NULL values and is an essential component of SQL for data analysis.