SQL: Count/ sum columns within a specific date range

by rahul   Last Updated August 14, 2019 18:26 PM

I have a table that roughly looks like this. There are thousands of rows.

booking_date   checkin_date    ...some other columns  ....    booking_value
22-mar-2016      29-mar-2016   ...........................         $150
01-apr-2016      17-may-2016   ...........................         $500
09-apr-2016      09-apr-2016   ...........................         $222
17-apr-2016      23-apr-2016  ...........................          $75
19-apr-2016      31-july-2016 ...........................          $690
03-May-2016      07-May-2016  .............................        $301
.
.
.
.

I am trying to calculate number of bookings per day and the value of bookings per day in April 2016. The second part is to calculate for how many bookings the booking_date and checking_date were the same.

I am very new to SQL. I can formulate the logic in paper, but can't seem to figure out how to proceed with the code.



Answers 3


You can try this below code-

SELECT CAST(booking_date AS DATE), 
COUNT(*) Number_of_Booking,
COUNT(
    CASE 
        WHEN CAST(booking_date AS DATE)  
            = CAST(checkin_date AS DATE) THEN 1
        ELSE NULL
    END
) count_with_same_date,
SUM(booking_value) booking_value -- Booking value has to be Number field
FROM your_table
WHERE YEAR(booking_date ) = 2016
AND MONTH(booking_date ) = 4
GROUP BY CAST(booking_date AS DATE)
mkRabbani
mkRabbani
August 14, 2019 17:19 PM

For the first question you can try Select booking_date ,count(*) as Number_of_bookings ,Sum(booking_value) as value From table_name Where booking_date between '01-apr-2016' and '30-apr-2016' group by booking_date:

Or you can use month() and year() function in filter.

For the second question try, Select booking_date ,checkin_date ,count(*) from table_name where booking_date=checkin_date group by booking_date, checkin_date

Abishek
Abishek
August 14, 2019 17:30 PM

I recommend the following query:

SELECT CAST(booking_date AS DATE), COUNT(*) as Number_of_Booking,
       SUM(CAST(booking_date AS DATE) = CAST(checkin_date AS DATE)) as count_with_same_date,
       SUM(booking_value) as booking_value
FROM t
WHERE booking_date >= '2016-04-01' AND
      booking_date < '2016-05-01'
GROUP BY CAST(booking_date AS DATE);

In particular, note the filtering on the dates. The direct comparisons allow MySQL to use an index.

The calculation of the number on the same date uses a nice feature of MySQL where boolean values are treated as numbers in a numeric context.

Gordon Linoff
Gordon Linoff
August 14, 2019 17:49 PM

Related Questions


sql select date range next year issue

Updated December 20, 2017 07:26 AM


Booking in MySQL and Laravel

Updated May 15, 2017 16:26 PM


Filter out rows with date range gaps in mysql

Updated May 05, 2017 08:26 AM