SQL SUM With GROUP BY

-- calculate the total amount spent by each customer

SELECT customer_id, SUM(amount) AS total_amount_spent
FROM Orders
GROUP BY customer_id;

SQL SUM() With GROUP BY

Consider the previous query where we calculate the total amount spent by each customer.

-- calculate the total amount spent by each customer

SELECT customer_id, SUM(amount) AS total_amount_spent
FROM Orders
GROUP BY customer_id;

Here, the SUM() function is used to aggregate the amount column. And the results are grouped by the customer_id column, giving us the total amount spent by each customer.

Here, the result set includes a column named total_amount_spent, which is an alias assigned to the result of the SUM() function.

SUM() With GROUP BY and JOIN

We can obtain the sum by combining two tables through a JOIN operation and then grouping the results.

Let's look at an example.

-- calculate the total amount spent by customers in each country

SELECT c.country, SUM(o.amount) AS total_amount_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.country;

Here, the SQL command calculates customer spending by joining the Customers and Orders tables on customer_id and grouping the results by country.

Visit SQL JOIN to learn more about the JOIN clause in SQL.

SUM() With GROUP BY and HAVING

We can use the SUM() function along with GROUP BY and apply the HAVING clause to filter the result set based on aggregate functions. For example,

-- retrieve total order amount and filter with HAVING

SELECT customer_id, SUM(amount) AS total_order_amount
FROM Orders
GROUP BY customer_id
HAVING SUM(amount) > 500;

Here, the SQL command calculates the total order amount for each customer who has placed orders.

And it filters the results to include only those customers who have spent more than 500.