Lesson 3

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 example, when analyzing a soccer match dataset, you might want to find the total number of goals scored by a team throughout a season. 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 matches database like ours and want to determine the total number of events of each season. That's a perfect opportunity to deploy the `SUM`

function. Let's see how it works!

Here's the basic syntax:

SQL`1SELECT SUM(expression) FROM table_name;`

`SUM(expression)`

: SUM() function expects at least one argument to specify what to sum. The correct usage is SUM(expression), where expression is typically a column name or a numerical value, such as SUM(1) to count occurrences or 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 number of trophies won in all seasons, you would use the `SUM`

function on the column that records the number of trophies.

SQL`1SELECT SUM(trophies_won) FROM Seasons; 2 3-- Output: 4-- SUM(trophies_won) 5-- ------------------- 6-- 28`

Let's see a practical example emphasizing our shared interest: soccer matches, and then break it down:

`SQL````
1SELECT Matches.season_id, SUM(1) AS TotalEvents
2FROM Matches
3JOIN MatchEvents ON Matches.match_id = MatchEvents.match_id
4GROUP BY Matches.season_id;
5
6-- Sneak peek of the output:
7-- | season_id | TotalEvents |
8-- |-----------|-------------|
9-- | 1 | 1 |
10-- | 2 | 8 |
```

It might seem complex, but don't worry! We're here to dissect it line by line.

`SELECT Matches.season_id, SUM(1) AS TotalEvents`

: In this query,`SUM(1)`

is used to count the number of events for each season. This utilizes the`SUM()`

function in a straightforward manner to aggregate the total count of events by season. This part of the query selects rows separately for each group according to the`season_id`

from the`Matches`

table and calculates the total number of events by summing 1 for each event in the group.`FROM Matches`

: This line informs SQL that our main table in this operation is`Matches`

.`JOIN MatchEvents ON Matches.match_id = MatchEvents.match_id`

: Here, we express our intention to join the`Matches`

table with the`MatchEvents`

table on the common field`match_id`

, essentially linking matches and their respective events. Note that`JOIN`

here is synonymous with`INNER JOIN`

, which ensures that only matching rows between the tables are selected.`GROUP BY Matches.season_id`

: Finally, we use the`GROUP BY`

clause to group the total events by seasons. You will learn more about`GROUP BY`

in the next unit!

When working with the `SUM`

function, there are a few common pitfalls to be aware of:

- Bear in mind that
`SUM`

works with numerical data. Using it on non-numerical columns will result in errors. - The
`AS`

keyword, as seen in the code, can make your output more readable by renaming the result of our`SUM`

operation. Don't forget to use it as necessary.

Great job! You've made excellent progress in mastering SQL functions. In this lesson, we learned about the `SUM`

function and how to use it to perform aggregate calculations in SQL. We applied it to our Lionel Messi database and calculated the total events in each season.

In the upcoming practice exercises, you'll get the opportunity to apply the `SUM`

function, deepen your understanding, and increase your confidence in handling it. Stay determined as you continue to unleash the power of SQL!