SQL - SELECT SUM

In SQL, the SELECT SUM statement is used to calculate the total sum of values in a numeric column. This powerful aggregate function allows you to summarize large sets of data, such as finding total revenue, product quantities, or any other numeric values in your database.

In this article, we will explain the SELECT SUM function through various examples and SQL operators to help us master this essential SQL function.

Understanding SQL SELECT SUM

In SQL, the SELECT SUM statement is used to calculate the total sum of values in a numeric column. It is an aggregate function that returns the sum of all the values in a specified column for the rows that meet a given condition.

• The SUM() function works with numeric data types.
  
• It ignores NULL values in the column.
  
• It can be combined with other SQL clauses like WHERE, GROUP BY, HAVING, and DISTINCT to perform advanced aggregations.

Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Examples of SQL SELECT SUM

Let's consider a sample table named orders that contains the following data:

Example 1: Using SUM() with One Column

In this example, we calculate the total quantity of products sold by summing up all the values in the quantity column. This is a basic use case of the SUM() function. Calculate the total quantity of all products sold.

Query:

SELECT SUM(quantity) AS total_quantity
FROM orders;

Output

Explanation:

The SUM(quantity) calculates the total of all values in the quantity column. The result is 31, which is the sum of quantities across all rows (2 + 5 + 10 + 7 + 4 + 3). This query helps in understanding the overall quantity sold.

Example 2: Using SUM() with an Expression

Expressions can be used within the SUM() function to perform more complex calculations. Here, we multiply the quantity by the price to calculate the total revenue generated from each product and then sum up the results. Calculate the total revenue by multiplying the quantity of each product by its price.

Query:

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

Output

Explanation:

The expression quantity * price calculates the revenue for each product by multiplying its quantity by its price. The SUM() function then adds these values to give the total revenue of 6550. This query is useful for understanding the overall financial performance.

Example 3: Using SUM() with GROUP BY

The GROUP BY clause allows us to group rows based on a specific column and perform aggregations like SUM() on each group separately. This is useful for generating category-wise summaries. Calculate the total quantity of products sold for each category.

Query:

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

Output

Explanation:

The GROUP BY category groups the rows by the category column. The SUM(quantity) function then calculates the total quantity for each category separately. For Clothing, the total is 11 (7 + 4), and for Electronics, it is 20 (2 + 5 + 10 + 3). This query helps in analyzing product sales across different categories.

Example 4: Using SUM() with DISTINCT

The DISTINCT keyword can be used within the SUM() function to ensure that only unique values are considered in the summation. This is useful when we want to avoid double-counting repeated value

Query:

SELECT SUM(DISTINCT price) AS total_distinct_price
FROM orders;

Output

Explanation:

The SUM(DISTINCT price) calculates the sum of distinct prices in the price column. The distinct prices are 800, 500, 20, 25, and 40. Their sum is 800 + 500 + 20 + 25 + 40 = 1360. This query helps in understanding the unique pricing structure in the dataset.

Example 5: Using SUM() with HAVING

The HAVING clause is used to filter grouped data based on an aggregate function like SUM(). This allows us to apply conditions to the results of GROUP BY operations. Find the categories where the total quantity sold is greater than 15.

Query:

SELECT category, SUM(quantity) AS total_quantity
FROM orders
GROUP BY category
HAVING SUM(quantity) > 15;

Output

Explanation:

The GROUP BY groups the rows by category, and the HAVING clause filters the result to include only those categories where the total quantity (SUM(quantity)) is greater than 15. In this case, only the Electronics category satisfies this condition with a total quantity of 20.

Conclusion

The SQL SELECT SUM function is essential for summarizing numerical data in a database. Whether we're calculating total quantities, revenues, or working with specific conditions using GROUP BY or HAVING clauses, this aggregate function provides efficient solutions for data aggregation. By mastering the use of SUM(), we can simplify complex data analysis and retrieve insightful summaries that help in decision-making.