SQL SUM With GROUP BY

In SQL, the aggregate function SUM() can be used with the GROUP BY clause to calculate sums for groups of rows based on one or more columns.

Example

-- 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 SQL command calculates the total amount spent by each customer.


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.

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges