Count unique in group by - Google Query

Question

I have this table:

A B
1 4
2 5
3 6
1 4
2 4
3 4

How could I output:

A count
1 1
2 2
3 2

i.e. count records with distinct value.

I have read that in mysql I could do:

select A, count(distinct B) from table group by A

How can I transform this to Google Spreadsheet Query language?

Answer 1

One way to do it is

1- In Column C1, enter a formula =Unique(A1:A6). This will give you the unique items in Column A

2- Then in Column D1, enter =COUNTUNIQUE(FILTER($B$1:$B$6,$A$1:$A$6=C1)) and drag it down

Answer 2

=QUERY(unique(A1:B7), "select Col1, count(Col2) group by Col1")

generally, the first part makes a "table" with only unique values and the select counts and groups.