Group events by day, count them and aggregate the amounts

Question

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

Answer 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'