JavaScript · Course navigation

JavaScript: GROUP BY and HAVING with Map

Total a column per group, keep the groups that pass a condition, and sort them.

On this page

Before you start

JavaScript arrays, Map, and destructuring.

Total a column per group, keep the groups that pass a condition, and sort them.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

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.

Code example

JavaScript

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);
}

Output

Ada 80
Bo 45

Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1const orders = [

2 { customer: "Ada", amount: 30 },

3 { customer: "Bo", amount: 20 },

4 { customer: "Ada", amount: 50 },

5 { customer: "Cy", amount: 5 },

6 { customer: "Bo", amount: 25 },

7];

9const totals = new Map();

10for (const order of orders) {

11 totals.set(order.customer, (totals.get(order.customer) ?? 0) + order.amount);

12}

14const result = [...totals]

15 .filter(([, sum]) => sum >= 30)

16 .sort((a, b) => b[1] - a[1]);

18for (const [customer, sum] of result) {

19 console.log(customer + " " + sum);

20}

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

Prepared trace of the published program. Some rows group repeated iterations.
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

Behind the scenes

How JavaScript processes this code

A JavaScript engine parses and runs the source. A function body runs when the function is called. console.log writes to the console.

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.