How do I include other columns in the results of a Google Sheets GROUP BY COUNT() query?

Question

Suppose I have a Google spreadsheet (Sheet1) with columns A:L. In another sheet (Sheet2) I want to aggregate data from Sheet1 as follows:

• The only relevant columns in Sheet1 are D, G, H, J, K.
• I want to GROUP BY J, SORT BY COUNT(J) and return columns D, G, H, J, K, COUNT(J) in descending order by COUNT(J).

I can get this to work when using only one column:

=QUERY('Sheet1'!J2:J,"select J, count(J) where J is not null group by J order by count(J) DESC label count(J) ''")

But I can't figure out how to make it also return columns D, G, H, K as well.

It's important to note that any rows that share a value at column J will share values at all of the other relevant columns in those rows.

Here is a link to a Google Sheet with sample data. Notice the formula in cell A2 of Sheet2.

Answer 1

Try this out:

=QUERY('Sheet1'!D2:J,
       "select D, G, H, K, J, count(J)
        where J is not null
        group by D, G, H, K, J
        order by count(J) DESC label count(J) ''")