QUERY › QUERY56 / 67

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeQUERY

QUERY: group by and sum

Mrs. Ratna runs a laundry business with three branches: Jakarta, Depok and Bekasi. Every daily transaction goes into one table, and at the end of each week she wants to see the total revenue per branch.

Task

In F1, write one QUERY formula over A1:D10 that shows each branch (B) with its total revenue (D), using group by.

Cells to fillF1:G4

Required functionsQUERY

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
Aggregate functions like sum() add up values per group defined by group by.
Hint 2
Every non-aggregated column in select must also appear in group by.
Hint 3
The pattern is select B, sum(…) group by ….

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

F1

=QUERY(A1:D10,"select B, sum(D) group by B",1)

Explanation

QUERY groups the rows by branch, then sum(D) adds up the revenue of each group: Bekasi 165,000, Depok 210,000 and Jakarta 410,000. Group by results are sorted ascending by the group column, and the aggregate column gets an automatic header, "sum Revenue" (you can rename it with the label clause). It works like a simple pivot table that updates as soon as new transactions arrive.

Starting worksheet: Sheet1
Row	A	B	C	D
1	Date	Branch	Service	Revenue
2	01/10/2024	Depok	Wash & dry	85,000
3	01/10/2024	Jakarta	Ironing	60,000
4	01/10/2024	Bekasi	Wash & dry	120,000
5	02/10/2024	Jakarta	Wash & dry	150,000
6	02/10/2024	Depok	Bed cover	70,000
7	02/10/2024	Bekasi	Ironing	45,000
8	03/10/2024	Jakarta	Bed cover	90,000
9	03/10/2024	Depok	Ironing	55,000
10	03/10/2024	Jakarta	Wash & dry	110,000
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result