title: Mysql select based on other select
body: <p>dears i have below query that's gets the count based on cases and it's working fine </p>
  
  <pre><code>select users.firstName,users.lastName,users.id,users.phoneNumber,
  count(CASE
          WHEN orders.`orderStatus` = 4 THEN 1 ELSE null END) As completed,
          count(CASE
          WHEN orders.`orderStatus`  = 5 THEN 1 ELSE null END) as CustomerCancelled,
          count(CASE
          WHEN orders.`orderStatus`  = 11 THEN 1 ELSE null END) as providerCancelled,
          count(`orders`.`createdAt`) as total,
          from users,providers,orders
          where
          `orders`.`providerId` = `providers`.`id`
          and
          users.id = `providers`.userId
          and
          `orders`.`createdAt` &gt;= (CURDATE() - INTERVAL 7 DAY)
          GROUP BY users.id;
  </code></pre>
  
  <p>what I need to add is to get the count for CustomerCancelled/total and show it with each record </p>
  
  <p>I tried to divide it like below but not working </p>
  
  <pre><code>select users.firstName,users.lastName,users.id,users.phoneNumber,
  count(CASE
          WHEN orders.`orderStatus` = 4 THEN 1 ELSE null END) As completed,
          count(CASE
          WHEN orders.`orderStatus`  = 5 THEN 1 ELSE null END) as CustomerCancelled,
          count(CASE
          WHEN orders.`orderStatus`  = 11 THEN 1 ELSE null END) as providerCancelled,
          count(`orders`.`createdAt`) as total,
          CustomerCancelled/total //// this is what i tried to do
          from users,providers,orders
          where
          `orders`.`providerId` = `providers`.`id`
          and
          users.id = `providers`.userId
          and
          `orders`.`createdAt` &gt;= (CURDATE() - INTERVAL 7 DAY)
          GROUP BY users.id;
  </code></pre>