JavaScript: GROUP BY and HAVING with Map
Total a column per group, keep the groups that pass a condition, and sort them.
const orders = [
{ customer: "Ada", amount: 30 },
{ customer: "Bo", amount: 20 },
{ customer: "Ada", amount: 50 },
{ customer: "Cy", amount: 5 },
{ customer: "Bo", amount: 25 },
];
const totals = new Map();
for (const order of orders) {
totals.set(order.customer, (totals.get(order.customer) ?? 0) + order.amount);
}
const result = [...totals]
.filter(([, sum]) => sum >= 30)
.sort((a, b) => b[1] - a[1]);
for (const [customer, sum] of result) {
console.log(customer + " " + sum);
}Ada 80 Bo 45
Explanation
SQL's GROUP BY puts rows with the same key into a group and computes an aggregate for each group. WHERE filters rows before grouping, while HAVING filters the groups after the aggregate has been computed.
The program groups orders by customer and sums the amounts in a Map, keeps the customers whose total is at least 30 (HAVING), and sorts them by total, largest first (ORDER BY ... DESC). The pipeline is the same one a database engine performs, in the same logical order.
Explore the dry run
const orders = [ { customer: "Ada", amount: 30 }, { customer: "Bo", amount: 20 }, { customer: "Ada", amount: 50 }, { customer: "Cy", amount: 5 }, { customer: "Bo", amount: 25 },]; const totals = new Map();for (const order of orders) { totals.set(order.customer, (totals.get(order.customer) ?? 0) + order.amount);} const result = [...totals] .filter(([, sum]) => sum >= 30) .sort((a, b) => b[1] - a[1]); for (const [customer, sum] of result) { console.log(customer + " " + sum);}Follow the example
Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.
View the complete dry-run table
| Step / line | What happens | State after step | Output so far |
|---|---|---|---|
| Add each order to its customer's running total: Ada 80, Bo 45, Cy 5. | totals = Ada 80, Bo 45, Cy 5 | — | |
| HAVING: Cy's total of 5 is dropped. | totals = Ada 80, Bo 45, Cy 5 | — | |
| ORDER BY total DESC: Ada first. | totals = Ada 80, Bo 45, Cy 5 | — | |
| Print the surviving groups. | totals = Ada 80, Bo 45, Cy 5 | Ada 80 Bo 45 |
Cost and common mistakes
O(n) to group plus O(g log g) to sort g groups.
- Filtering before grouping (WHERE) and after grouping (HAVING) give different results. Choose the one that matches the question.
- ?? supplies 0 only for undefined or null, so a legitimate total of 0 is kept.
Try it yourself
How would you also count the orders per customer?
Reveal answer and reasoning
Store an object such as { sum, count } in the Map and increment both fields for each order.