Query that combines the contents of 3 columns, but also filters by a date in a 4th column

Question

This is an example of my data that is located on a sheet called Data:

Date	Fruit 1	Fruit 2	Fruit 3
1/1/2020	Apple	Mango	Apple
2/1/2021	Cherry	Banana	Pear
2/1/2021	Orange
1/1/2022	Apple	Cherry	Orange
2/1/2022
2/1/2022	Pear	Banana
21/1/2022	Cherry	Banana

Desired output:

Filtered to only show data between 1/1/2022 and 31/12/2022, sorted by the Count. The start and end dates are located in cells C2 and E2

Suggestions	Count
Banana	2
Cherry	2
Apple	1
Orange	1
Pear	1

I have achieved what I want in 2 steps:

First is a query that filters the data by dates specified and removes any rows that have no data in the 'Fruit 1' column. This formula is located in Cell D5.

=QUERY (Data!A:D,"SELECT A, B, C, D WHERE A > date '"&TEXT(C2,"yyyy-mm-dd")&"' and J <= date '"&TEXT(E2,"yyyy-mm-dd")&"' AND B<>''",1)

D5	E5	F5	G5
1/1/2022	Apple	Cherry	Orange
2/1/2022	Pear	Banana
21/1/2022	Cherry	Banana

then running a second query on those results that combines the 'Fruit 1', 'Fruit 2' and 'Fruit 3' columns and returns the data sorted by count as shown in my desired output above.

=QUERY({E5:E;F5:F;G5:G}, "select Col1, count(Col1) where Col1<>'' group by Col1 order by count(Col1) desc label (Col1) 'Sorted List', count(Col1) 'Count'",1)

What I would really like to do is to do this in one query, but I can't find information on how to run a second query on the data that is generated by the first query... or how to approach it.

I hope this makes sense!

Answer 1

Your data is in a "wide" format, which makes it harder to work with. You may want to Insert > Sheet and unpivot the data into a "tall" format, like this:

Date	Category	Data
2020-01-01	Fruit 1	Apple
2020-01-01	Fruit 2	Mango
2020-01-01	Fruit 3	Apple
2021-01-02	Fruit 1	Cherry
2021-01-02	Fruit 2	Banana
2021-01-02	Fruit 3	Pear
2021-01-02	Fruit 1	Orange
2022-01-01	Fruit 1	Apple
2022-01-01	Fruit 2	Cherry
2022-01-01	Fruit 3	Orange
2022-01-02	Fruit 1	Pear
2022-01-02	Fruit 2	Banana
2022-01-21	Fruit 1	Cherry
2022-01-21	Fruit 2	Banana

Once you have that, the report can be created with this query() formula:

=query( 
  { Data2!A1:C }, 
  "select Col3, count(Col3) 
   where 
     date " & text(C2, "'yyyy-MM-dd'") & " <= toDate(Col1) 
     and toDate(Col1) <= date " & text(E2, "'yyyy-MM-dd'") & " 
   group by Col3 
   order by count(Col3) desc", 
  1 
)

If you do not want to insert a helper sheet, you can unpivot and query in one go with this formula:

=query( 
  let( 
    table, Data!A1:D, 
    numColsToRepeat, 1, 
    numColsToSqueeze, columns(table) - numColsToRepeat, 
    headers, { offset(table, 0, 0, 1, numColsToRepeat), "Category", "Data" }, 
    categories, offset(table, 0, numColsToRepeat, 1, numColsToSqueeze), 
    colsToRepeat, offset(table, 1, 0, rows(table) - 1, numColsToRepeat), 
    colsToSqueeze, offset(table, 1, numColsToRepeat, rows(table) - 1, numColsToSqueeze), 
    reduce( 
      headers, colsToSqueeze, 
      lambda( 
        result, cell, 
        if( 
          cell = "", 
          result, 
          let( 
            rowIndex, row(cell) - row(colsToSqueeze) + 1, 
            columnIndex, column(cell) - column(colsToSqueeze) + 1, 
            { 
              result; 
              { 
                index(colsToRepeat, rowIndex), 
                index(categories, 1, columnIndex), 
                cell 
              } 
            } 
          ) 
        ) 
      ) 
    ) 
  ), 
  "select Col3, count(Col3) 
   where 
     date " & text(C2, "'yyyy-MM-dd'") & " <= toDate(Col1) 
     and toDate(Col1) <= date " & text(E2, "'yyyy-MM-dd'") & " 
   group by Col3 
   order by count(Col3) desc", 
  1 
)

To unpivot the data into a "tall" format, use the longish let() in there, without the query wrapper.