CODEWITHSUNDEEP
google-sheets
Djabx
Djabx

Reputation: 745

How to concat query results?

I've got data like this:

A          | B
-----------------------
Date       | Data
30/06/2015 | 1.2
01/07/2015 | 2
01/07/2015 | 3
02/07/2015 | 2
02/07/2015 | 3

And I write a simple query like this:

=query(A:B; "select YEAR(A) || ' ' || month(A), SUM(B)
    group by YEAR(A), month(A)")

But I've got a "Value Error", so I write this

=query(A:B; "select YEAR(A), MONTH(A), SUM(B)
    group by YEAR(A), month(A)")

And it work well, but, how can I concat two values in one cell ?

I though of this:

=QUERY(Journal!A:B;"select A, SUM(B)
    group by A
    format A 'yyyy-MM'")

But the format operation is done AFTER the group by so it's like the group by never was.

Any idea ?

[Solution]

=query({Arrayformula(text(Foo!A:A; "yyyy-MM")) \Foo!B:B}; "select Col1, sum(Col2) group by Col1"; -1)

Because my local is "french" I've to use the '\' in the Arrrayformula and ; instead of ','...

Upvotes: 1

Views: 8215

Answers (1)

JPV
JPV

Reputation: 27292

Use the ampersand (&) to concatenate in arrayformula:

=ArrayFormula(if(len(A:A), year(A:A)&" "&month(A2:A),))

or if you don't want the space between year and month:

=ArrayFormula(if(len(A:A), year(A:A)&month(A2:A),))

EDIT:

If you want to query and sum, try:

=query({Arrayformula(text(Journal!A:A, "yyyy-MM")),Journal!B:B}, "select Col1, sum(Col2) where Col2 is not null group by Col1", 0)

or in locale that use semi-colons as argument separators:

=query({Arrayformula(text(Journal!A:A; "yyyy-MM"))\Journal!B:B}; "select Col1, sum(Col2) where Col2 is not null group by Col1"; 0)

Example spreadsheet

Upvotes: 1

Related Questions