How to I count a range in sql?-CodePudding

I have a data that looks like this:

 $ Time        : int  0 1 5 8 10 11 15 17 18 20 ...
 $ NumOfFlights: int  1 6 144 91 504 15 1256 1 1 578 ...

Time col is just 24hr time. From 0 up all the way until 2400

What I hope to get is:

   hour      |   number of flight
-------------------------------------
   1st       |        240
   2nd       |        223
   ...       |        ...
   24th      |        122

Where 1st hour is from midnight to 1am, and 2nd is 1am to 2am, and so on until finally 24th which is from 11pm to midnight. And number of flights is just the total of the NumOfFlights within the range.

I've tried:

dbGetQuery(conn,"
           SELECT
             flights.CRSDepTime AS Time,
             COUNT(flights.CRSDepTime) AS NumOnTimeFlights
           FROM flights
           GROUP BY CRSDepTime/60 
           ")

But I realise it can't be done this way. The results that I get will have 40 values for time.

> head
  Time NumOnTimeFlights
1   50             6055
2  105             2383
3  133              674
4  200              446
5  245              266
6  310               34

> tail
   Time NumOnTimeFlights
35 2045            48136
36 2120           103229
37 2215            15737
38 2245            36416
39 2300            15322
40 2355             8018

CodePudding user response：

If your CRSDepTime column is an integer encoded time like HHmm then CRSDepTime/100 will extract the hour.

       SELECT
         CRSDepTime/100 AS hh,
         COUNT(flights.CRSDepTime) AS NumOnTimeFlights
       FROM flights
       GROUP BY CRSDepTime/100