1

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?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.