Mastering the SUM Function for Aggregate Calculations

Welcome back! So far, we've explored the COUNT function to count rows and the DISTINCT keyword to ensure data uniqueness. Now, we will dive into another powerful aggregate function in SQL: SUM.

Aggregate functions help us summarize and analyze data. For instance, when analyzing an online shopping dataset, you might want to find the total revenue generated from the sales of products. The SUM function allows you to add up values in a column, providing valuable insights.

Let's get started by understanding the tools and environment you'll need for this lesson.

The SUM function is an aggregate operation in SQL, used to calculate the total sum of a numerical column in a database. Think of it as a mathematical operation that adds up all the numbers in a set—simple, yet profound!

The syntax is as follows: SUM(column) where column is the name of the column for which you want to calculate the sum.

You might be wondering, "When would I need to use SUM?" Consider a situation where you have a products database like ours and want to determine the total revenue generated by the sales of all products. That's a perfect opportunity to deploy the SUM function. Let's see how it works!

Here's the basic syntax:

SELECT SUM(expression) FROM table_name;

• SUM(expression): The SUM() function expects an argument to specify what to sum. The correct usage is SUM(expression), where the expression is typically a column name or a numerical value, such as SUM(column_name) to sum up values from a specific column.
• table_name: The table containing the column you want to sum.

For example, if you want to find the total revenue generated by the products sold, you would use the SUM function on the product prices.

SELECT SUM(Products.product_price) AS TotalRevenue
FROM OrderItems
JOIN Products ON OrderItems.product_id = Products.product_id;

-- Output:
--  TotalRevenue 
-- --------------
--      16782.60 

Let's see a practical example emphasizing our shared interest: online shopping data, and then break it down:

SELECT Products.category_id, SUM(1) AS TotalItemsSold
FROM OrderItems
JOIN Products ON OrderItems.product_id = Products.product_id
GROUP BY Products.category_id;

-- Output:
-- | category_id | TotalItemsSold |
-- |-------------|----------------|
-- |           1 |            270 |
-- |           2 |             60 |
-- |           3 |            120 |
-- |           4 |             60 |
-- |           5 |             90 |

Let's break it down:

1. SELECT Products.category_id, SUM(1) AS TotalItemsSold: In this query, SUM(1) is used to count the number of items sold for each product category. This utilizes the SUM() function in a straightforward manner to aggregate the total count of items sold by category. This part of the query selects rows separately for each group according to the category_id from the Products table and calculates the total number of items sold by summing 1 for each sold item in the group.
2. FROM OrderItems: This line informs SQL that our main table in this operation is OrderItems.
3. JOIN Products ON OrderItems.product_id = Products.product_id: Here, we express our intention to join the OrderItems table with the Products table on the common field product_id, linking order items and their respective products.
4. GROUP BY Products.category_id: Finally, we use the GROUP BY clause to group the total items sold by product categories.