How to filter records with an aggregate function SUM in SQL

Problem

You have a data table and you want to filter records based on the result of an aggregate function, specifically the SUM function.

Sample data

Let's consider a sample data table named 'Orders' with the following columns: 'OrderID', 'CustomerID' and 'Amount'.

Our objective is to filter out the customers who have a total order amount greater than 200.

MySQL Solution

SELECT CustomerID, SUM(Amount) as TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > 200;

PostgreSQL Solution

The solution for PostgreSQL is exactly the same as the solution for MySQL for this problem.

SELECT CustomerID, SUM(Amount) as TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > 200;

Explanation

In both MySQL and PostgreSQL, the solution is same. The SUM function is used to add up all the 'Amount' values for each 'CustomerID'. The GROUP BY clause is used to divide the 'Orders' into groups with the same 'CustomerID'. The HAVING clause is used to filter the result of the aggregation. In this case, it filters out the groups where the total 'Amount' is greater than 200. The result of this query will be the 'CustomerID' and the total 'Amount' for customers who have a total order amount greater than 200.