Christian Haller Christian Haller - 2 months ago 5
MySQL Question

Join table on itself for distinct dates and count()

I'm on MYSQL 5.6 with a single table called ride

+---------+-------+---------+
| Date | CarId | Airport |
+---------+-------+---------+
| 2001-11 | 1 | JFK |
| 2001-11 | 2 | JFK |
| 2001-11 | 3 | LAX |
| 2001-12 | 1 | JFK |
| 2001-12 | 2 | JFK |
| 2001-12 | 3 | JFK |
+---------+-------+---------+


In the column date, there are a lot of dates with duplicates.
For a list of all distinct dates I run this query:

SELECT DISTINCT date from ride;


And I got two dates ✅

My goal is to know the number of cars at every recorded time on a specific airport.
So I think I have to join the same table.

SELECT DISTINCT r.date, count(x.CarId) as car from ride r JOIN ride x on r.date = x.date WHERE x.airport = "LAX";


Here I got only one row with date 2001-11 and 3 cars. ❌

What is the correct query to get something like this:

2001-11 LAX 1 car


Or is it impossible with the data structure?
2001-12 LAX 0 cars

Answer

I'm assuming that all your dates and airports are represented in the data. The problem is that you don't have at least one recorded instance of each combination of the two as with LAX in 2001-12.

This is a common question. The key is to generate all combinations that you want to report on, which is why the cross join. Then use an outer join to attached the data you have to that template.

select d.Date, a.Airport, count(r.CarId) /* you may want count(distinct r.CarId) */
from
    (select distinct Date from ride) d
    cross join
    (select distinct Airport from ride) a
    left outer join ride r
        on r.Date = d.Date and r.Airport = a.Airport
group by d.Date, a.Airport