1

See the below data on which i am doing query.

result = alasql("SELECT HourOfDay, Count(HourOfDay) TotalCount from ? where ReportedDate = DATE('1/20/2012') group by HourOfDay ", [data])
<table style="width:100%">
  <tr>
  <th>ReportedDate</th> <th>HourOfDay</th>
  </tr>
  <tr>   
    <td>2012-01-20 09:30:00.000</td>
    <td>9</td>   
  </tr>
  <tr>
    <td>2012-01-21 09:30:00.000</td>
    <td>11</td>   
  </tr>
  <tr>
    <td>2012-01-11 09:30:00.000</td>
    <td>9</td>   
  </tr><tr>
    <td>2012-01-20 09:30:00.000</td>
    <td>5</td>   
  </tr><tr>
    <td>2012-01-20 09:30:00.000</td>
    <td>9</td>   
  </tr><tr>
    <td>2012-01-13 09:30:00.000</td>    
    <td>7</td>   
  </tr><tr>
    <td>2012-01-20 09:30:00.000</td>    
    <td>9</td>   
  </tr><tr>
    <td>2012-01-20 09:30:00.000</td>   
    <td>10</td>   
  </tr>
</table>

i need total count of HourOfDay where date is "1/20/2012", i am querying on this data with given query but i am not getting any result so please help me out,i am new to alasql.

This is my array

data=[
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'9'},
{'ReportedDate': '2012-01-21 09:30:00.000','HourOfDay':'10'},
{'ReportedDate': '2012-01-11 09:30:00.000','HourOfDay':'1'},
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'4'},
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'5'},
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'6'},
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'8'},
{'ReportedDate': '2012-01-13 09:30:00.000','HourOfDay':'12'}];
Mahi Kalyankar
  • 257
  • 5
  • 18
  • Its helpful if you post what error you are getting. Please note you are missing a `]` after `[data`. Please checkout https://github.com/agershun/alasql/wiki/Html and see if that brings you forward. – mathiasrw Mar 15 '16 at 13:25
  • @mathiasrw ya i know that, while writing the Q i did that mistake, but in my code it is proper. when i am doing this query on **data** it is giving me only 1 result like **HourOfDay = undifined** & **TotalCount = 0**. so i am not getting proper output – Mahi Kalyankar Mar 15 '16 at 13:40
  • @mathiasrw tell me that this query is proper or not **alasql("SELECT HourOfDay, Count(HourOfDay) TotalCount from ? where ReportedDate = DATE('1/20/2012') group by HourOfDay ", [data])** i dont want compare date, i want data on given particular date. if i remove where clause it is giving me all the ReportedDates HourOfDay count. but i want only one dateTotalCount – Mahi Kalyankar Mar 15 '16 at 13:41
  • It would be awesome if you could post the javascript array you are working on instead of the html. – mathiasrw Mar 15 '16 at 16:23
  • @mathiasrw HTML snippet is my array on which i am doing query – Mahi Kalyankar Mar 16 '16 at 06:56

1 Answers1

1

I suggest using a custom function. It could be something like:

alasql.fn.theDate(s){
    return s.split(' ').shift();
} 

hours = alasql("VALUE OF SELECT SUM(HourOfDay) FROM ? WHERE theDate(ReportedDate) = '2012-01-20'", [data])

At some point you might find it interesting to get the sum of each day all at once:

totalHoursPerDay = alasql("SELECT theDate(ReportedDate) as date, SUM(HourOfDay) as hours FROM ? GROUP BY theDate(ReportedDate)", [data])
mathiasrw
  • 610
  • 4
  • 10