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