Dividing new created columns

Issue

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;

Solution

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

Answered By – Ergest Basha

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published