Google spreadsheet query multiple columns with a count column-CodePudding

I have a table like this:

Animal      ID  eye color
-------------------------
Rabbit      90  blue
Rabbit      90  brown
Cat         91  blue
Cat         91  green
Squirrel    92  brown

What I have is

=QUERY(A2:C6;"select A,count(A) group by A")

which returns two columns. Now I want to add the ID column to it.

The desired outcome basically is:

Animal      ID  Count
----------------------
Cat         91    2
Rabbit      90    2
Squirrel    92    1

I realize I can do a

=QUERY(A2:C6;"select A,B ")

But can't combine that with a count on A nor a count on B.

Is there some not too complicated way to do that?

CodePudding user response：

If the same animal has the same ID, you can group by both:

=QUERY(A2:C6,"select A,B,count(A) group by A,B")

or choose max or min or avg value of ID per group:

=QUERY(A2:C6,"select A,min(B),count(A) group by A")