Query a count of unique attributes in a table

Question

I have a table like this:

+--------+-------+--------+-------+
| attr1  | attr2 | attr3  | attr4 |
+--------+-------+--------+-------+
| purple | wine  | clear  |  10.0 |
| red    | wine  | solid  |  20.0 |
| red    | beer  | cloudy |  10.0 |
| purple | ale   | clear  |  34.0 |
| blue   | ale   | solid  |  16.0 |
+--------+-------+--------+-------+

that i want to transform like this:

+--------+-------+-------+-------+-------+
|        | attr1 | attr2 | attr3 | attr4 |
+--------+-------+-------+-------+-------+
| purple |     2 |       |       |       |
| red    |     2 |       |       |       |
| blue   |     1 |       |       |       |
| wine   |       |     2 |       |       |
| beer   |       |     1 |       |       |
| ale    |       |     2 |       |       |
| clear  |       |       |     2 |       |
| solid  |       |       |     2 |       |
| cloudy |       |       |     1 |       |
| 10.0   |       |       |       |     2 |
| 20.0   |       |       |       |     1 |
| 34.0   |       |       |       |     1 |
| 16.0   |       |       |       |     1 |
+--------+-------+-------+-------+-------+

This pivoted or cross-table will show me the count of each attribute value in their respective columns.

How do i use the Google Query language to display such a cross-table?

Answer 1

Well if the data were laid out in two columns it would be straightforward e.g. for something like this

Attrib  Column
Red     1
Red     1
Green   1
Blue    1
Beer    2
Ale     2
Ale     2

you could use a query like

=query(A:B,"select A,count(A) where A<>'' group by A pivot  B")

So the problem is to organise OP#s data into two columns.

This can be done by what is by now a fairly standard split/join/transpose technique

=ArrayFormula(split(transpose(split(textjoin("|",true,if(A2:D="","",A2:D&" "&column(A2:D))),"|"))," "))

Giving

You could either run the query on the result of this or combine the two like this

 =ArrayFormula(query({"Attrib","Number";split(transpose(split(textjoin("|",true,if(A2:D="","",column(A2:D)&"-"&A2:D&" "&column(A2:D))),"|"))," ")},"Select Col1,count(Col1) group by Col1 pivot Col2"))

I have joined the column number to the attribute e.g. 1-blue so that it sorts into the right order. If you don't like it, you could get rid of it using regexreplace.

Edit

Slightly shorter formula - I didn't need to put the headers in separately:

=ArrayFormula(query(split(transpose(split(textjoin("|",true,if(A2:D="","",column(A2:D)&"-"&A2:D&" Attr"&column(A2:D))),"|"))," "),
"Select Col1,count(Col1) group by Col1 pivot Col2",0))

Edit 2

I was being a bit thick there, should have used first row of OP's data as attribute labels instead of column numbers

=ArrayFormula(query(split(transpose(split(textjoin("|",true,if(A2:D="","",column(A2:D)&"-"&A2:D&" "&A1:D1)),"|"))," "),
"Select Col1,count(Col1) group by Col1 pivot Col2",0))

Edit 3

Should have chosen a better pair of delimiters

    =ArrayFormula(query(split(transpose(split(textjoin("😊",true,if(A2:D="","",column(A2:D)&"-"&A2:D&"🍺"&A1:D1)),"😊")),"🍺"),
"Select Col1,count(Col1) group by Col1 pivot Col2",0))