Linked Questions

Popular Questions

I have two tables that are something as follows:

WORKDAYS
DATE       | WORKDAY_LENGHT |
-----------+----------------+
12-05-2018 |       8        |
13-05-2018 |       6.5      |
14-05-2018 |       7.5      |
15-05-2018 |       8        |
ACCIDENTS
       TOD       | SEVERITY  | 
-----------------+-----------+
12-05-2018 12:00 |  minor    | 
12-05-2018 15:00 |  minor    |
13-05-2018 08:00 |  severe   |
13-05-2018 12:00 |  severe   |
14-05-2018 10:30 |  severe   |

And I need a result that is as follows:

WORKDAYS
DATE       | WORKDAY_LENGHT | ACCIDENTS_COUNT|
-----------+----------------+----------------+
12-05-2018 |       8        |       2        |
13-05-2018 |       6.5      |       2        |
14-05-2018 |       7.5      |       1        |
15-05-2018 |       8        |       0        |

What I so far have tried is this:

SELECT DISTINCT 
     w.date, 
            (
             SELECT 
                    COUNT(*) 
             FROM 
                    accidents a 
             WHERE 
                   date(w.date) = date(a.tod)
             ) 
      AS accidents_count 
 FROM 
      workdays w

Which gives me an answer that is somewhat in the right direction. Something like this:

WORKDAYS
DATE       | WORKDAY_LENGHT | ACCIDENTS_COUNT|
-----------+----------------+----------------+
12-05-2018 |       8        |       1        |
12-05-2018 |       8        |       1        |
13-05-2018 |       6.5      |       1        |
13-05-2018 |       6.5      |       1        |
14-05-2018 |       7.5      |       1        |
15-05-2018 |       8        |       0        |

This is sqlite, so the date values are stored as strings. The date function therefore should make them just dates, right? Or is that the one causing problems?

Related Questions