Google Sheets - GROUP BY and ORDER BY on different column

Question

I got a table in this format

Name Datum
A   01.01.2019
B   17.03.2020
C   18.03.2020
C   01.04.2020

I get this table output from this query:

=QUERY(Anrufe!$1:$1000;"Select A,B where A is not null ORDER BY B ASC label A 'Name', B 'Datum'")

I am trying to change the query, so that it performs a group by. This is what it should look like

Name  Datum        count
A     01.01.2019   1
B     17.03.2020   1
C     01.04.2020   2

But when i add a group by and add a aggregation function to the select it still throws an error.

=QUERY(Anrufe!$1:$1000;"Select A,B, min(B) where A is not null group by A ORDER BY B ASC label A 'Name', B 'Datum'")

I want the query to choose the youngest Date, that is in the column B. But it seems that the QUERY-Parser forces me to add the column B to the group by. This results in the same problem, that i manually have to count the records with the same names.

Anyone has any idea how to solve that? Already checked those links:

• Google sheets query order by SUM
• Google Sheets Query: Possible to Group / Order by without Select?
• Google Sheets query - Multiple “where” conditions with “group by” and “order by”
• I got this message (CANNOT_GROUP_WITHOUT_AGG) from a simple query

Answer 1

I think you want this:

=QUERY(Anrufe!$1:$1000,"Select A,min(B),count(A) where A is not null group by A ORDER BY min(B) ASC label A 'Name', min(B) 'Datum'")