Search code examples
google-sheetspivotgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

Handling gaps that result from a Google Sheets query that group rows by date

In Google Sheets, I have a range containing list of events along with how many times they take place each day. I'm displaying this data on another sheet in columns, by querying out each event and giving it a column. The problem I run into is that some dates don't have an occurrence of an event, and this doesn't add a corresponding row with a count of '0' into my source data, breaking how I render my data elsewhere.

Here's an example of the source format (notice that Oct 2nd doesn't have any Event 2 data):

2019-10-01,Event 1,12
2019-10-01,Event 2,8
2019-10-02,Event 1,16
2019-10-03,Event 1,7
2019-10-03,Event 2,21

...and then I run queries like this to populate a column:

=QUERY(event_data, "SELECT C WHERE B = 'Event 1' GROUP BY A)

Now if I do this for Event 2, it will follow up the Oct 3rd number right after Oct 1st, and the gap would make it look like those results were from Oct 2nd when I show these two events side by side:

Date       Event 1  Event 2
2019-10-01   12        8 
2019-10-02   16        7
2019-10-03   21

Is there any reasonable way to solve this within Google Sheets? I know you can't really do an left/inner joins in a query.

I think the better way to do this is rewrite the SQL query I used to populate the source range, so that it returns a row like this in the first place:

2019-10-02,Event 2,0

Just wondering if there's any way around that.

Solution

  • you can pivot stuff in QUERY:

    =QUERY(A1:C, "select A,sum(C) where A is not null group by A pivot B", 0)

    0