Mysql select based on other select

135
November 21, 2018, at 10:00 PM

dears i have below query that's gets the count based on cases and it's working fine

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` >= (CURDATE() - INTERVAL 7 DAY)
        GROUP BY users.id;

what I need to add is to get the count for CustomerCancelled/total and show it with each record

I tried to divide it like below but not working

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` >= (CURDATE() - INTERVAL 7 DAY)
        GROUP BY users.id;
Answer 1

You can't use aliases in the SELECT part of the query. You need to explicitly write out the expression:

select users.firstName,
       users.lastName,
       users.id,
       users.phoneNumber,
       COUNT(CASE WHEN orders.`orderStatus` = 4 THEN 1 END) aS completed,
       COUNT(CASE WHEN orders.`orderStatus` = 5 THEN 1 END) AS CustomerCancelled,
       COUNT(CASE WHEN orders.`orderStatus`  = 11 THEN 1 END) AS providerCancelled,
       COUNT(`orders`.`createdAt`) AS total,
       COUNT(CASE WHEN orders.`orderStatus`  = 5 THEN 1 END) / COUNT(`orders`.`createdAt`) AS ratio_cancelled
FROM users
JOIN providers
JOIN orders
WHERE `orders`.`providerId` = `providers`.`id`
  AND users.id = `providers`.userId
  AND `orders`.`createdAt` >= (CURDATE() - INTERVAL 7 DAY)
GROUP BY users.id;

Note that you don't need an ELSE null in your CASE expressions as that is the default. Also you should write explicit JOIN statements rather than use the deprecated comma style implicit JOIN.

Rent Charter Buses Company
READ ALSO
android GlobalBus.getBus().isRegistered(this) not working

android GlobalBus.getBus().isRegistered(this) not working

I have used EventBus in the projectI have a navigation drawer from which I am navigatinng through fragments

130
Android Activity Lifecycle called twice when starting new Activity

Android Activity Lifecycle called twice when starting new Activity

I am basically starting new Activity which contains a WebViewBut this WebView Activity's lifecycle called twice when first run

222