1

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:

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Zephil
  • 13
  • 3

1 Answers1

0

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'")

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • When i tried this i got this error: `error parsing formula` – Zephil Apr 14 '20 at 19:29
  • Sorry, you need to use ; in your locale: =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'") – Tom Sharpe Apr 14 '20 at 19:42
  • WOW! Thank you. Finally your code with a small change (max instead of min) did it: `=QUERY(Anrufe!$1:$1000;"Select A,max(B),count(A) where A is not null group by A ORDER BY max(B) ASC label A 'Name', max(B) 'Datum'")` – Zephil Apr 14 '20 at 19:52