How to use a query within a query?

December 11, 2024

As a fitness trainer I have 2 sheets in Google Sheets to track invoiced and consumed training sessions:

Sheet ‘CalendarEvents’

source  customerId  customerName    eventType         eventName     dateTime    cancelled
Calendly    JEAN    Jean Claude     Group training    SMALL GROUP   15-12-2024 11:00 (Sun)  FALSE
Google      CHUCK   Chuck Norris    Personal training 1-ON-1        18-12-2024 0:00 (Wed)   FALSE
Google      CHUCK   Chuck Norris    Group training    SMALL GROUP   18-12-2024 0:00 (Wed)   TRUE
Calendly    CHUCK   Chuck Norris    Group training    SMALL GROUP   15-12-2024 10:00 (Sun)  FALSE
Calendly    CHUCK   Chuck Norris    Group training    SMALL GROUP   15-12-2024 10:00 (Sun)  FALSE

Sheet ‘CustomerInvoices’

InvoiceNr InvoiceDate PaymentModel  Activity            CustomerID  CustomerName Sessions
0001    02/05/2021  Subscription    Personal training   CHUCK   Chuck Norris     5
0002    02/05/2021  Voucher         Group training      CHUCK   Chuck Norris     10
0003    02/05/2021  Voucher         Group training      CHUCK   Chuck Norris     10
0004    02/05/2021  Voucher         Group training      JEAN    Jean Claude      5

I’m now trying to find a way to create an overview of the sessions invoiced and the sessions consumed, with the source data in both sheets.

Desired result

In another sheet it would be great to get a summary of all invoiced, consumed and remaining training sessions.

CustomerID  CustomerName         Invoiced   Consumed    Remaining
CHUCK       Chuck Norris            20         2           18
JEAN        Jean Claude Vandamme    5          1           4

Currently I got as far as making a sum of all invoiced session:

=QUERY(
  CustomerInvoices, 
  "SELECT E, F, SUM(G) 
   WHERE D = 'Group training' 
   GROUP BY E, F 
   LABEL E 'CustomerID', F 'CustomerName', SUM(G) 'Invoiced'"
)

How is it possible to count the Consumed sessions for each client (Activity = ‘Group training’ and not cancelled)? Remaining sessions would be Invoiced minus Consumed.

What I’ve tried already fails:

=QUERY(
  CustomerInvoices, 
  "SELECT E, F, SUM(G), ( SELECT COUNTIFS(CalendarEvents!B, InvoiceContent!A, CalendarEvents!D, 'Group training', CalendarEvents!G, FALSE)
       WHERE CalendarEvents!D = 'Group training'
     )
   WHERE D = 'Group training' 
   GROUP BY E, F 
   LABEL E 'CustomerID', F 'CustomerName', SUM(G) 'Invoiced'"
)

Test sheet

Thank you for your help.

>Solution :

Try the following formula-

=QUERY(LET(x,QUERY(
  CustomerInvoices, 
  "SELECT E, F, SUM(G) 
   WHERE D = 'Group training' 
   GROUP BY E, F"),
y,BYROW(x,LAMBDA(r,COUNTIFS(CalendarEvents!B:B,CHOOSECOLS(r,1),CalendarEvents!C:C,CHOOSECOLS(r,2),CalendarEvents!D:D,"Group training",CalendarEvents!G:G,FALSE))),
z,MAP(CHOOSECOLS(x,3),y,LAMBDA(a,b,IFERROR(a-b,0))),
HSTACK(x,y,z)),"offset 1 LABEL Col1 'CustomerID', Col2 'CustomerName', Col3 'Invoiced', Col4 'Consumed', Col5 'Remain'",0)