0

Here is my below query which gives the count and group by each hour

SELECT ADD_SECONDS(start_time,- MINUTE(start_time) * 60  - SECOND(start_time)) as time , to_integer(to_varchar(start_time, 'DD')) as day , count(*) as count FROM SYSTEM.TABLE where start_time >= '2016-01-01 00:00:00' and start_time <= '2016-01-01 23:59:59'  and place_id=1 group by ADD_SECONDS(start_time,- MINUTE(start_time) * 60  - SECOND(start_time)),to_integer(to_varchar(start_time, 'DD')) 
order by ADD_SECONDS(start_time,- MINUTE(start_time) * 60  - SECOND(start_time))

But at the time period 11:00 pm to 12:00 pm I have no count so instead of not returning the row I want it to return the row with 0.

So when I went through some search I found that COALESCE can help so I tried with

SELECT COALESCE (( SELECT ADD_SECONDS(start_time,- MINUTE(start_time) * 60  - SECOND(start_time)) as time , to_integer(to_varchar(start_time, 'DD')) as day , count(*) as count FROM SYSTEM.TABLE where start_time >= '2016-01-01 00:00:00' and start_time <= '2016-01-01 23:59:59'  and place_id=1 group by ADD_SECONDS(start_time,- MINUTE(start_time) * 60  - SECOND(start_time)),to_integer(to_varchar(start_time, 'DD')) 
    order by ADD_SECONDS(start_time,- MINUTE(start_time) * 60  - SECOND(start_time))
), 0);

But it did not also work. Any help is appreciated.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
John
  • 305
  • 2
  • 4
  • 18
  • You are going to have to use some sort of dates table, e.g. http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql. I'm guessing that you would want something that would look for the minimum/ maximum date times in your source data, then create a row for each hour in between? – Richard Hansell Apr 04 '17 at 11:44
  • No I group the data by hour basis and its working fine but my problem is that I do not have any count between the time period 11:00 to 12:00 pm.So the row does not appear.But I want that row to be appeared and show the count as 0 – John Apr 04 '17 at 12:06
  • This is exactly why you need a "dates" table, that is independent of your query. Imagine that you had a query that returned number of customers who joined each week day, there were no customers who joined on a Sunday, but you didn't want to lose a day. You could select the seven week days, then LEFT JOIN these to your data. This way Sunday would be included, even though there would be no matching data to display. The same principle applies here. – Richard Hansell Apr 05 '17 at 10:22

1 Answers1

2

In order to 'have a row' for every hour, you need to create the hour-rows independent of your data. This could be done e.g. by using an auxiliary table that has a row for every hour.

SAP HANA provides shared default aux. tables that can be used for that purpose (these tables also facilitate date/time conversion via lookup): M_TIME_DIMENSION... . SAP HANA Docu M_TIME_DIMENSION

Just select the range of time values you are interested in from these tables and outer join it with your aggregated actual values.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29