Count unique in group by - Google Query

Question

I have this table:

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?

You can use `QUERY` in google spreadsheet, but it doesnt like count(distinct b) for some reason. — Pankaj Jaju, Mar 16 '14 at 14:34
possible duplicate of [count distinct values in spreadsheet](http://stackoverflow.com/questions/14380882/count-distinct-values-in-spreadsheet) — kenorb, Aug 28 '15 at 22:41
@kenorb no it's not a duplicate, the question specifically refers to Google Query. — Ricky McMaster, Mar 03 '18 at 19:31

score 10 · Answer 1 · answered Jun 30 '17 at 06:43

10

=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.

answered Jun 30 '17 at 06:43

marc meyer

530
6
6

score 8 · Answer 2 · answered Mar 16 '14 at 14:27

8

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

enter image description here

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

enter image description here

answered Mar 16 '14 at 14:27

Pankaj Jaju

5,371
2
25
41

is possible to use that formula with AJAX? I am using this https://developers.google.com/chart/interactive/docs/querylanguage – sites Mar 16 '14 at 21:07
I'm not really an expert in this; but I've seen people applying formulas and fetching the values. Would suggest some Google Rnd – Pankaj Jaju Mar 16 '14 at 21:34

Benoit Gramond · Answer 3 · 2017-09-06T15:16:43.173

If you want to do it with a QUERY, it is possible. Indeed, the QUERY function will give you a table as a result; You have to set another QUERY to set the range of the final function.

In your case:

=QUERY(
UNIQUE(QUERY(A1:B7;"SELECT * WHERE A is not null ORDER BY A"));
"SELECT Col1, count(Col2) WHERE Col1 is not null GROUP BY Col1")

As you can see, the first QUERY set the table for the second one. This is why you have to use ColX and not A for the final QUERY.

score 0 · Answer 4 · edited Dec 04 '20 at 08:36

0

You just add sum function on the query.

SUM(QUERY(A1:B7,"SELECT COUNT(B) GROUP BY B LABEL COUNT(B) ''"))

edited Dec 04 '20 at 08:36

MaxiGui

6,190
4
16
33

answered Dec 03 '20 at 17:38

Michael Adiwibowo

1

Note: answers that are very brief and/or are questions back to the poster should probably be comments. You only need 50 rep points to comment under a question - could you move this there? – Tyler2P Dec 04 '20 at 10:07

Count unique in group by - Google Query

4 Answers4