Join table on itself for distinct dates and count()

Question

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

This is what helper tables are for and left joins. Create a helper table. See http://stackoverflow.com/a/37739514 — Drew, Sep 06 '16 at 16:04
You say "every recorded time" but you're really just showing months in the sample data. What are you regarding as a distinct time? — shawnt00, Sep 06 '16 at 16:07
Also I would seriously consider doing a `date` column even if you peg them at `2001-11-01`. You would at least be able to use good date math functions — Drew, Sep 06 '16 at 16:07
In any case the question is too unclear what you are getting at. — Drew, Sep 06 '16 at 16:08

score 0 · Answer 1 · answered Sep 06 '16 at 15:59

0

Try this

SELECT Date , count(CarId) From ride GROUP BY Date , Airport ;

answered Sep 06 '16 at 15:59

Wasiq Muhammad

3,080
3
16
29

shawnt00 · Accepted Answer · 2016-09-21T21:32:50.060

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

Join table on itself for distinct dates and count()

2 Answers2