1

I have a long list of date times (timestamps) in column A, attached to some data in column B. I want to group this data by day, obtaining the count of events and the totals for each day. For example, from this input

+-------------------+----+
| 4/1/2016 15:50:52 | 50 |
| 4/1/2016 16:35:22 | 45 |
| 4/1/2016 18:39:11 | 65 |
| 4/2/2016 7:42:04  | 40 |
| 4/2/2016 15:45:49 | 55 |
| 4/3/2016 2:00:29  | 60 |
| 4/3/2016 6:46:50  | 45 |
| 4/3/2016 13:43:00 | 65 |
| 4/4/2016 13:18:15 | 40 |
| 4/4/2016 13:43:19 | 55 |
+-------------------+----+

I want to obtain

+----------+--------+------+
|   date   | count  | sum  |
+----------+--------+------+
| 4/1/2016 |      3 |  160 |
| 4/2/2016 |      2 |   95 |
| 4/3/2016 |      3 |  170 |
| 4/4/2016 |      2 |   95 |
+----------+--------+------+

Knowing about the scalar function toDate of the Google Query language, I tried the following:

=query(A:B, "select toDate(A), count(A), sum(B) group by toDate(A)")

I received the following error. How to overcome this?

Unable to parse query string for Function QUERY parameter 2:
SELECT_WITH_AND_WITHOUT_AGGA

1

The error message says that the same column cannot be selected both with and without aggregation. One solution is to move the count function to another column:

=query(A:B, "select toDate(A), count(B), sum(B) group by toDate(A)")

Another solution, which is also applicable when there is no column B, is to duplicate column A before querying: {A:A, A:B} is the array with three columns in which the column A appears twice. One can refer to these columns as Col1, Col2, Col3 in the query string. The following formula returns the desired table:

=query({A:A, A:B}, "select toDate(Col1), count(Col2), sum(Col3) where Col1 is not null group by toDate(Col1) label toDate(Col1) 'date'")

For readability, the same query string with linebreaks:

select toDate(Col1), count(Col2), sum(Col3) 
where Col1 is not null 
group by toDate(Col1) 
label toDate(Col1) 'date'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy