1

I have constructed an alasql query with a array as the data source.. the columns in my table are( with 1000+ rows):

City, TravelDate, Q1, Q2, Q3, Q4

1, 2015-05-31, 6, 6, 5, 5

My requirement is to find the averages for the questions (under 6) grouped by City and Year and month.

PLUS ONLY FOR A CERTAIN DATE RANGE

[] contains the index number of the columns in array

so far my query :

var res = alasql('SELECT [11] as City, [0], AVG([1]), AVG([2]),AVG([3]),AVG([2]) FROM ?D WHERE [1] <= 5 AND [2]<= 5 AND [3]<= 5 AND [4]<= 5 GROUP BY [11],[0]'[data]);

The query above works however:

It does not work if I place WHERE [0] >= '2014-01-01' AND [0] <= '2015-05-31'

Notes:

I have tried using double quotes "" but still does not work

Also the date column in the data array is constructed using JavaScript date method in a for loop with new Date()

The rows contain date results including any day so there can be multiple rows with the same date for given month and year.

Does anyone have an idea how to contruct my query so that my output is like

City,TravelDate,AvgQ1,AvgQ2,AvgQ3,AvgQ4

So in other words grouped by City and (Year&Month) (rather than City,Year,Month) within my date range requirement

skha177
  • 11
  • 3

1 Answers1

1

You can use DATE() function to convert JavaScript date to "YYYY-MM-YY' format:

WHERE DATE([0]) >=  "2014-01-01" AND DATE([0]) <= "2015-05-31"

For grouping by year and month you can use:

GROUP BY YEAR([0]), MONTH([1])
agershun
  • 4,077
  • 38
  • 41