CODEWITHSUNDEEP
mysqlcase
Faisal
Faisal

Reputation: 441

Mysql select based on other select

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;

Upvotes: 0

Views: 18

Answers (1)

Nick
Nick

Reputation: 147176

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.

Upvotes: 1

Related Questions