COUNT DISTINCT in QUERY of Google Sheets

Question

This is the query I am trying to perform with the QUERY function:

=QUERY(DATA!A2:H;"select Col1,Col2,Col3, count(Col4) group by Col1,Col2,Col3 ")

I expected it to count Col4 values, but that the repeated ones would only be counted once.

Answer 1

You may try:

=query({DATA!A2:C\byrow(DATA!A2:C;lambda(Σ;if(countif(byrow(DATA!A2:index(DATA!C:C;row(Σ));lambda(Λ;join(;Λ)));join(;Σ))=1;1;)))};
   "Select Col1, Col2, sum(Col4), count(Col3) group by Col1, Col2 label sum(Col4) '',count(Col3)'' ")