Google Sheets – Group Events by Day, Count and Aggregate Amounts

google sheetsgoogle-sheets-query

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

Best Answer

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'

Related Solutions

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

This can be done by using the running total as an intermediate result (you can put in some column further on the right and/or make it hidden, if you don't want to see it).

I'll use column C for the cumulative total and column D for 7-day total; finding the average is simple division.

The formula for cumulative total is found here. I'll use it in this form

C2 =ARRAYFORMULA(IF(LEN(A2:A), SUMIF(ROW(B2:B),"<="&ROW(B2:B), B2:B), ""))

The conditional statement about LEN(A2:A) prevents output in rows where no data is present.

And here is the computation of 7-day running total:

D2 =ARRAYFORMULA(IF(LEN(A2:A), C2:C-IFERROR(VLOOKUP(A2:A-7, A2:C, 3), 0), ""))

The key player here is the VLOOKUP function which find the latest date that is less than or equal to "a week ago", and returns the cumulative total for it. This represents the total of "old" entries, which should be subtracted from the current total so that only the entries from the last 7 days are counted.

The IFERROR statement handles the case when there is nothing to subtract, i.e., all preceding dates are within the 7-day window.

Here is sample output to verify correctness:

+-----------+------+---------------+-------------+
|   Date    | Data | Running Total | 7-day total |
+-----------+------+---------------+-------------+
| 1/4/2015  |    5 |             5 |           5 |
| 1/5/2015  |    3 |             8 |           8 |
| 1/9/2015  |    1 |             9 |           9 |
| 1/11/2015 |    9 |            18 |          13 |
| 1/12/2015 |    4 |            22 |          14 |
| 1/13/2015 |    2 |            24 |          16 |
| 1/27/2015 |   64 |            88 |          64 |
+-----------+------+---------------+-------------+

Google Sheets Query – Average Time on Group By

Indeed, out of the six data types supported by query (string, number, boolean, date, datetime, and timeofday), the avg function can be used only on numbers. So you have to convert duration to number (the function N does this) and average that:

=query({A2:C7, arrayformula(N(C2:C7))}, "select Col1, sum(Col2), avg(Col4) group by Col1")

Here the array constructed by {A2:C7, arrayformula(N(C2:C7))} has four columns, the fourth one being the datetime converted to numbers. This is the column which gets averaged. You will need to format the column into which the average is returned as duration.

Best Answer

Related Solutions

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

Google Sheets Query – Average Time on Group By

Related Topic