# 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.

Tags :

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
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
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
August 14, 2019 17:49 PM