Modules

Let's talk about the SUM function in SQL, using a relatable example from a popular social media platform like Instagram. Imagine you're analyzing data from Instagram, focusing on the total number of likes received on posts.

The SQL SUM Function

In SQL, SUM is like a calculator that adds up all the numbers in a specific column. Remember, it only works with numbers. So, do use that to SUM user_id (just saying because I've seen that a lot!)

Suppose you have a table instagram_posts with columns like post_id, user_id, likes, and post_date. Each row in the table represents a post, and the likes column shows how many likes each post received.

Now, if you want to know the total number of likes across all posts, you'd use the SUM function:

SELECT SUM(likes)
FROM instagram_posts;

This query adds up all the likes from every post in the table.

Handling Nulls in SUM

An interesting thing about SUM is how it deals with null values. Unlike COUNT, which needs a bit of thought around nulls, SUM just treats them as zero. So, if some posts don't get any likes (or the likes are missing), SUM simply ignores those and keeps on adding the rest.

This is not the case for COUNT which when you specify the column name, will only count the values that are NOT NULL.

Calculate inside the SUM function

The SUM function is not limited to just adding up values directly from a column; you can perform various calculations inside the SUM function itself.

Let's say you're working with Amazon's sales data and want to calculate the total revenue, but you only have the quantity for each order and the price per unit. Here is the sample data below:

In this scenario, for each row, the total revenue will be equal to the quantity times the price per unit. And you can use the SUM with this internal calculation to measure the total revenue of the company:

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

Tada 🎉!

Now that you know a lot about SUM, let's practice!