Dividing new created columns-CodePudding

orders_table:

orders_id_column   | user_id_column | final_status_column
----------------------------------------------------
1                  | 4455           | DeliveredStatus
2                  | 4455           | DeliveredStatus
3                  | 4455           | CanceledStatus
4                  | 8888           | CanceledStatus

I want to calculate the total number of orders, and the number of Canceled orders by user_id, and then the cocient between these two, to arrive to something like is:

user_id | total_orders | canceled_orders | cocient
---------------------------------------------------
4455    | 3            | 1               | 0.33
8888    | 1            | 1               | 1.00

I managed to create the first two columns, but not the last one:

SELECT 
    COUNT(order_id) AS total_orders,
    SUM(if(orders.final_status = 'DeliveredStatus', 1, 0)) AS canceled_orders
FROM users
GROUP BY user_id;

CodePudding user response：

You can use an easy approach :

SELECT 
    user_id,
    COUNT(order_id) AS total_orders,
    SUM(CASE WHEN final_status = 'CanceledStatus' THEN 1 ELSE 0 END ) AS 
    canceled_orders,
   SUM(CASE WHEN final_status = 'CanceledStatus' THEN 1 ELSE 0 END ) /COUNT(order_id) 
    as cocient
FROM users
GROUP BY user_id;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/136

CodePudding user response：

You could just use a sub-query.

Then you can refer to the newly created columns, as the outer query exists in a different scope (one where the new columns now exist).

(Thus avoids repeating any logic, and maintaining DRY code.)

SELECT
  user_id,
  total_orders,
  cancelled_orders,
  cancelled_orders / total_orders
FROM
(
  SELECT
    user_id,
    COUNT(order_id) AS total_orders,
    SUM(if(orders.final_status = 'DeliveredStatus', 1, 0)) AS canceled_orders
  FROM
    users
  GROUP BY
    user_id
)
  AS per_user

Note, selecting from the users table appears to be a typo in your example. It would appear that you should select from the orders table...