mercredi 5 août 2020

How select count distinct (unique truckers) without group by function and maybe without using Having (not sure about last)

I have a task, but couldn't solve it:

There are many truckers in the world and they have to work (travel) between cities.
We have logs of these travels in our database in 2 tables:

  1. trucker_traffic
    tt_id (key)
    date
    starting_point_coordinate
    destination_coordinate
    traveller_id
    event_type ('travel', 'accident')
    parent_event_id (For 'accident' event type it's tt_id of the original travel. There might be few accidents within one travel.)
  2. trucker_places
    coordinate (key)
    country
    city

Please write an SQL query to pull the number of all unique truckers who travelled more than once from or to London city in June 2020.
In the same query pull the number of these travels who got into an accident.

Example of my tries

SELECT
    count(distinct(tt.traveller_id)),
        
FROM trucker_traffic tt

JOIN trucker_places tp
    ON tt.starting_point_coordinate = tp.coordinate
     OR tt.destination_coordinate   = tp.coordinate
WHERE 
    tp.city = 'London'
    AND month(tt.date) = 6
    AND year(tt.date) = 2020
    
GROUP BY tt.traveller_id

HAVING count(tt.tt_id) > 1

But it's select count distinct truckers with grouping and works only if I had one tracker in db

For second part of task (where I have select number of travels with accident - I think that good to use function like this

SUM(if(count(tt_id = parent_event_id),1,0))

But I'm not sure

Aucun commentaire:

Enregistrer un commentaire