Google Sheets – Querying for the Last Results with Group By

google sheetsgoogle-sheets-query

I've got the following data sheet:

date(A)     source(B)   cost(C)     totals(D)       val(E)
9-8-2019    Magic       0.00        0               0.00
9-8-2019    Stick       54.72       6820            8.02
9-8-2019    Foo         -41.36      17841           0.00
9-8-2019    Bar         29.66       79652           0.47
9-8-2019    Magic       29.79       79937           0.47
9-8-2019    Stick       0.00        0               0.00
9-8-2019    Foo         -41.52      17906           0.00
9-8-2019    Bar         54.72       6820            8.02
7-8-2019    Magic       29.79       79937           0.47
7-8-2019    Stick       0.00        0               0.00
7-8-2019    Foo         -41.52      17906           0.00
7-8-2019    Bar         54.72       6820            8.02
7-8-2019    Magic       21.10       79937           0.47
7-8-2019    Stick       0.00        0               0.00
7-8-2019    Foo         -40.52      17906           0.00
7-8-2019    Bar         55.72       6820            8.02

To get all the results of today its as easy as =query(Data!A:E; "SELECT A, B, C, D WHERE A = date'"&TEXT(TODAY(); "yyyy-mm-dd")&"'";0)

I would like to group it by source and select the latest values (cost, totals, val). But when applying the first part of the query (GROUP BY B) the error said: CANNOT_GROUP_WITHOUT_AGG.

What is the best way to achieve this?

Best Answer

That's a classic mistake when using Group By in a =QUERY

check this https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/

The GROUP BY clause is used with aggregate functions to summarize data into groups, in the same way a pivot table does. {...} Note, every column in the SELECT clause (i.e. before the GROUP BY) must either be aggregated (e.g. counted, min, max) or appear after the GROUP BY clause (e.g. column C in this case).

Now there is a simple method to group the result by source with =SORT

=sort(query(Data!A:E; "SELECT A, B, C, D WHERE A = date'"&TEXT(TODAY(); "yyyy-mm-dd")&"'";0);2;true)

Parameters are (range, column, order), you can replace 2 by something like ;Column(B:B); to futur-proof the sheet.

I would like to group it by source and select the latest values (cost, totals, val).

What do you mean ? please provide a sample for us to better understand what you expect, I'll edit this answer to adress this issue.

Related Topic