Group by on a Date..

[#1]

Hello all!

I have a table with currency rates per day and I would like to calculate an average rate per month. Why doesnt my script below work?

The currency rate table (tmpCurrencyRates) looks like this:

Date, USD,DKK,NOK

2009-02-03 1,232 4,123 2,341

2009-02-04 1,543 4,765 2,567

---------------------------------------

script:

CurrencyRates:

load

date(date#(left(Date, 7), 'YYYY-MM'), 'YYYY-MM') as CurrencyPeriod,

avg(USD) as aUSD,

avg(DKK) as aDKK,

avg(NOK) as aNOK

resident tmpCurrencyRates

group by CurrencyPeriod;

Br

Cristian

[#2 · Accepted solution]

try with

CurrencyRates:

load

date(date#(left(Date, 7), 'YYYY-MM'), 'YYYY-MM') as CurrencyPeriod,

avg(USD) as aUSD,

avg(DKK) as aDKK,

avg(NOK) as aNOK

resident tmpCurrencyRates

group by date(date#(left(Date, 7), 'YYYY-MM'), 'YYYY-MM');

[#3]

Worked great! Thank you!

Br

Cristian