Generate resultset according to report

Question

How do I generate the following reports using MYSQL?

My table 'reservations' looks like this:

id | arrival   | created.
1  | 2014-3-30 | 2014-3-1
2  | 2014-3-31 | 2014-3-2
3  | 2014-3-28 | 2014-3-2
4  | 2014-3-01 | 2014-3-1
5  | 2014-3-01 | 2014-3-1

I want to generate the following two aggregation reports for the "arrival" column for the whole month as shown below:

(1)

     arrival     | count  | total_monthly_arrivals
     2014-03-01  | 2      | 5
     2014-03-02  | 0      | 5
     2014-03-03  | 0      | 5
     ...
     2014-03-30  | 1      | 5
     2014-03-31  | 1      | 5

(2)

    January     | 5
    ...
    March       | 5
    ...
    December    | 0      | 5

I want these 2 result sets. It generates date according & month according report and generate result set in these form.

I tried to use group by with count in first resultset but it doesn't retrieve for a date that does not exist. Again I wanna put month condition such that I choose month. where month = '02' or something like this. Is that possible?

score 0 · Answer 1 · edited May 23 '17 at 12:29

My SQLFiddle should answer both parts of your question: http://sqlfiddle.com/#!2/9f130/31

This will return the arrival date with a count of how many people are coming that day and how many monthly arrivals there are

select distinct(r.arrival) as arrival_date,
count(r.arrival) as total_per_day,
sa.month_total as total_arrival_per_month
from reservations as r
  ,(select substr(arrival,6,2) as month,
    count(substr(arrival,6,2)) as month_total
    from reservations
    group by month) as sa
where substr(r.arrival,6,2) = sa.month
group by arrival_date,total_arrival_per_month;

This will return the month of the year and how many people are booked for that month and how many are arriving that month. (Updated with the suggestion from agrizzo.)

select MONTHNAME(STR_TO_DATE(substr(r.arrival,6,2), '%m')) as arrival_date,
sa.month_total as total_arrival_per_month
from reservations as r
  ,(select substr(arrival,6,2) as month,
    count(substr(arrival,6,2)) as month_total
    from reservations
    group by month) as sa
where substr(r.arrival,6,2) = sa.month
group by arrival_date,total_arrival_per_month;

There is however, no way for me to give you every day/month of the year without a fully qualified data set, provided by and prefilled by you. That's on you to do and provide us with.

However, you can check this thread. Get a list of dates between two dates and leverage their information with my queries to get your desired results.

You can probably shorten your code using MONTHNAME() function — AgRizzo, Mar 27 '14 at 21:38
It doesn't retrieve or set recordset according to non-existent dates and month. Means its just fetch out march and november. if it doesn't exist for Januray or FEB it should show count as 0. Similar logic I want for date that if record doesn't exist corresponding to certain date. It should show 0 against that date. — Sankalp, Mar 28 '14 at 04:48
"There is however, no way for me to give you every day/month of the year without a fully qualified data set, provided by and prefilled by you. That's on you to do and provide us with." — the_pete, Mar 28 '14 at 18:00

Generate resultset according to report

1 Answers1