CODEWITHSUNDEEP
sqlprimary-keyrate
Picot
Picot

Reputation: 85

calculate rate of attribute by id sql

First, this is my table schema:

order_id, product_id, add_to_cart_order, reordered

My problem is calculate the rate of reordered by product. So we can see "add_to_cart_order" is useless, I don't know for "order_id". "reordered" can be have '1' and '0' value.

For the moment, I can have the count of "reordored" by product_id with 

SELECT 
    product_id, 
    COUNT(reordered) 
FROM 
    train 
WHERE
    reordered = '1' 
GROUP BY 
    product_id;

and the count of occurrence of a product with 

SELECT 
    product_id, COUNT(*) 
FROM
    train  
GROUP BY 
    product_id;

I tried 

SELECT 
    t1.product_id, 
    COUNT(t1.product_id) / (SELECT COUNT(reordered) 
                            FROM train t2 
                            WHERE t2.reordered = '1' 
                              AND t1.product_id = t2.product_id 
                            GROUP BY product_id) 
FROM
    train t1 
GROUP BY
    t1.product_id;

But it takes too much time (I don't know if it's the right request because I don't have results yet)

Upvotes: 0

Views: 87

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think the simplest method is to use AVG():

SELECT product_id, 
       AVG(CASE WHEN reordered = '1' THEN 1.0 ELSE 0 END) 
FROM train 
GROUP BY product_id;

If reordered is really a number that only takes on the values 0 and 1, then you can further simplify this to either:

SELECT product_id, AVG(reordered)
FROM train 
GROUP BY product_id;

or:

SELECT product_id, AVG(reordered * 1.0)
FROM train 
GROUP BY product_id;

The second is needed in databases where the average of an integer is returned as an integer.

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this elegant

SELECT t1.product_id, SUM(CASE WHEN reordered = 1 THEN 1 ELSE 0 END) / COUNT(t1.product_id)
FROM train t1 
GROUP BY t1.product_id;

Upvotes: 1

DCSQL777
DCSQL777

Reputation: 76

Is this what you are looking for?

SELECT Product_id, SUM(CASE WHEN reordered=1 THEN 1 ELSE 0 END ) /
COUNT(*) AS ReorderedRate
FROM
train
GROUP BY Product_id

Upvotes: 2

EchoMike444
EchoMike444

Reputation: 1692

this will compute for each product_id : the number of lines in train cnt_prod the number of lines in train cnt_prod_reorder that was reordered 

SELECT t1.product_id, COUNT(t1.product_id) as cnt_prd,
                      COUNT(case when t.1.reordered='1' then  1 else NULL end ) as cnt_prd_reord 
from train t1 group by t1.product_id;

So after you can do :

select st.product_id , st.cnt_prd , st.cnt_prd / st.cnt_prd_reord
from (
     SELECT t1.product_id, COUNT(t1.product_id) as cnt_prd,
                           COUNT(case when t.1.reordered='1' then  1 else NULL end   ) as cnt_prd_reord 
     from train t1 group by t1.product_id 
) as st ;

Upvotes: 0

Related Questions