Google Query Language: Count multiple columns based on conditions for each column and grouped by date

Question

I would like use the QUERY function / Google Query Language in Google Sheets to count multiple columns based on conditions for each of these columns and grouped by date.

This is how the database looks (it is a list of emails sent out with a timestamp and info about if they were opened and clicked on):

date	opened	clicked
2021-09-14 4:30:06	TRUE	FALSE
2021-09-14 4:30:10	TRUE	TRUE
2021-09-15 4:30:10	FALSE	FALSE
2021-09-15 4:30:11	TRUE	TRUE
2021-09-15 4:30:18	FALSE	FALSE

This is the outcome that I need (I basically want to know how many emails per day were opened and clicked on):

date	count opened	count clicked
2021-09-14	2	1
2021-09-15	1	1

I know that I can achieve this for one column using the following query:

select
toDate(A),
count(B) where B=TRUE
group by toDate(A)

but if I try to apply the query to two columns it does not work:

select
toDate(K),
count(B) where B=TRUE,
count(C) where C=TRUE
group by toDate(K)

Is there elegant way to achieve this using a single formula?

Answer 1

You can convert the data in the query input into the type you want - in this case just remove all FALSE values from the table

=(query(ARRAYFORMULA({A:A,SUBSTITUTE(B:C,"FALSE","")}), "select toDate(Col1),count(Col2),count(Col3) where Col1 is not null group by toDate(Col1)"))