Count bookings and cancellations per member (select in subquery) That will be my very first query using subquery ever but I already have problems.

Let's take a look at my tables:

user
   - id
   - name

user_visit
   - id
   - user_id
   - visit_type_id (BOOKED, CANCELED)
   - visit_starts_at
   - visit_ends_at

type_of_visit
   - id
   - type


And the result of it should be as follows:

['name' =&gt; 'John Doe', 'bookings' =&gt; 138, 'cancellations' =&gt; 30],
['name' =&gt; 'Jane Smith', 'bookings' =&gt; 47, 'cancellations' =&gt; 12],
['name' =&gt; 'Fleur V.', 'bookings' =&gt; 12, 'cancellations' =&gt; 1]


So it should count how many bookings and cancellations had user.

So far I have started with the following query:

SELECT
    u.name AS `name`,
    COUNT(booking.id) AS `bookings`
FROM `user` AS `u`, 
      (SELECT 
            *
        FROM `user_visit` AS `uv`
        INNER JOIN type_of_visit AS `tov` 
            ON uv.visit_type_id = tov.id
        WHERE tov.type = 'BOOKED'
      ) AS `booking`
 GROUP BY u.id


Unfortunately it does not work properly because it was returning only one user, so something went wrong. Anyway you guys have more experience and you probably know how would you handle this, so anyone?
I hope that query is self-explaining enough.

SELECT u.name AS `name`
     , IFNULL( z.bookings, 0 ) AS bookings
     , IFNULL( q.cancellations, 0 ) AS cancellations 
  FROM user AS u 

  LEFT JOIN 
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `bookings`
         FROM user_visit AS uv
        WHERE uv.type = 'BOOKED'
        GROUP BY uv.user_id 
     )
     AS z 
     ON z.user_id = u.id

  LEFT JOIN 
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `cancellations`
         FROM user_visit AS uv
        WHERE uv.type = 'CANCELED'
        GROUP BY uv.user_id
     ) 
     AS q 
     ON q.user_id = u.id
;