How do you reference a cell containing a date in a Query?

Question

I have a table to keep track of hours put in working on an Interior Design project. The table looks something like this:

8/16/2015   6:30:00 PM     8:00:00 PM    1.50   Floorplan
8/19/2015   10:00:00 AM    10:30:00 AM   0.50   Layout
8/19/2015   12:00:00 PM    3:30:00 PM    3.50   staircase

In another table, I want to consolidate all the dates into a single row that sums up the total amount of hours worked that day and concatenates the descriptions of what was worked on. So I'd like something like this:

    8/16/2015   1.50    Floorplan
    8/19/2015   4.00    Layout, staircase

The first part of making this happen, is writing a Query that could group the dates. This wasn't too difficult and I made it happen with

=QUERY(A2:E4, "select A, sum(D) group by A")

However, since I want to add the concatenated descriptions in the next cell, I've been trying to write a separate query for those cells. However I'm having a major issue, because I'm trying to look at the date on each row but I can't reference the cell from inside the query. So for example, I'm trying to insert this function:

=JOIN(", ", TRANSPOSE(UNIQUE((QUERY(A2:F4, "select E where day(B) = day(A6)))))

This function should get all the unique descriptions corresponding to a particular day (for simplicity ignoring the year and month), and join them up into a single cell. However it is failing because day(A6) is returning nothing even though there is a date in A6 from the previous query.

So the question is, how do I properly use a reference to another cell in a Google Sheets query?

Answer 1

Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:

=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))

You don't need transpose, because join works equally well with one-dimensional arrays of either direction: vertical or horizontal.

---

That said, I find a solution using filter slightly more readable:

=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))