选择并分组MySql表

Question

I need a little help to count and group some MySql table records according of a range of dates given. The table strcutre is this:

In advance, thank you for your help

Answer 1

This shoud give you the result that you need:

SELECT
  d1.dt,
  COUNT(DISTINCT t1.recid) AS departures,
  COUNT(DISTINCT t2.recid) AS returns,
  COUNT(DISTINCT t1.recid) + COUNT(DISTINCT t2.recid) AS total
FROM (SELECT departure_date AS dt FROM yourtable
      UNION SELECT return_date FROM yourtable) d1
  LEFT JOIN yourtable t1 ON d1.dt = t1.departure_date
  LEFT JOIN yourtable t2 ON d1.dt = t2.return_date
GROUP BY
  d1.dt

The first subquery will return all dates, not duplicated, present in your table (departures and returns).

I'm then trying to join each date to a departure date, using a LEFT JOIN. I'm then counting the DISTINCT t1.recid that make the join succeed..

I'm then trying to join each date to a return date, using LEFT JOIN. Total is the sum of both counts.

Fiddle is here.