Mysql calculates percentage of repeated rows

Question

So, I'm trying to calculate the number of re-orders in my system in a restaurant. This is defined as the number of users (based on their email address, eo_email) who ordered more than once from this restaurant. Examples under the diagram

Here is a table representing my restaurants

CREATE TABLE IF NOT EXISTS `lf_restaurants` ( `r_id` int(8) NOT NULL AUTO_INCREMENT, `r_name` varchar(128) DEFAULT NOT NULL, PRIMARY KEY (`r_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; INSERT INTO `lf_restaurants` (`eo_id`, `eo_ref_id`) VALUES ('1', 'Restaurant X'), ('2', 'Cafe Y'); 

And this is my order table

 CREATE TABLE IF NOT EXISTS `ecom_orders` ( `eo_id` mediumint(9) NOT NULL AUTO_INCREMENT, `eo_ref_id` varchar(12) DEFAULT NOT NULL, `eo_email` varchar(255) DEFAULT NOT NULL, `eo_order_parent` int(11) NOT NULL, PRIMARY KEY (`eo_id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; INSERT INTO `ecom_orders` (`eo_id`, `eo_ref_id`, `eo_email`, `eo_order_parent`) VALUES ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '1'), ('', '', ' [e-mail] ', '2'), ('', '', ' [e-mail] ', '2'), ('', '', ' [e-mail] ', '2'), ('', '', ' [e-mail] ', '2'); 

So, restaurant X (r_id 1) has 10 orders. Users [e-mail] and [e-mail] ordered at this restaurant several times, and [e-mail] , [e-mail] and [e-mail] only ordered once, so it will need to return 40%

Cafe Y (r_id 2) has 4 orders. The user [e-mail] ordered twice, the users [e-mail] and [e-mail] only ordered once, so he would need to return 33%

I'm not sure that posting what I already have will be very good, because I continue to work in “Subquery has more than one result” or if I wrap this subquery in my own dummy query with count, this will not allow me to use fields that I need from a main request like r_id. But here goes:

 SELECT r_name, (SELECT COUNT(*) AS cnt_users FROM ( SELECT * FROM ecom_orders WHERE eo_order_parent = r_id GROUP BY eo_email ) AS cnt_dummy ) AS num_orders, (SELECT COUNT(*) AS cnt FROM ecom_orders WHERE eo_order_parent = r_id GROUP BY eo_order_parent, eo_email ) AS num_rep_orders FROM lf_restaurants ORDER BY num_orders DESC 

The subquery num_orders says that it does not recognize r_id, as I assume this is related to the order of things

The num_rep_orders subquery is returned as multiple lines, but in fact I want this to return with only one value, which I could do if I made it as a num_orders subquery, but then ran into r_id, and there is no problem.

So my question is: how do I get these values ​​that I need, without starting into a subquery, have more than 1 row, and r_id does not exist?

Then from these two values ​​I can work out a percentage, and everything should be genuine :) Any help is greatly appreciated!

Answer 1

So, restaurant X (r_id 1) has 10 orders. The users [e-mail] and [e-mail] have been ordered from this restaurant several times, and [e-mail] , [e-mail] and [e-mail] only ordered once, so they will need to return 40%

Cafe Y (r_id 2) has 4 orders. The user [e-mail] ordered twice, the users [e-mail] and [e-mail] ordered only once, so he will need to return 33%

Good. So let's start by getting the number of repeat customers.

 SELECT eo_order_parent, eo_email, COUNT(eo_email) AS orders FROM ecom_orders GROUP BY eo_order_parent, eo_email HAVING orders > 1; 

And the total number of different customers

 SELECT eo_order_parent, COUNT(eo_email) FROM ecom_orders GROUP BY eo_order_parent; 

But we can do it in one go:

 SELECT eo_order_parent, SUM(CASE WHEN orders > 1 THEN 1 ELSE 0 END) AS repeats, SUM(1) AS total FROM ( SELECT eo_order_parent, eo_email, COUNT(*) AS orders FROM ecom_orders GROUP BY eo_order_parent, eo_email ) AS eo_group_1 GROUP BY eo_order_parent; 

This gives:

 +-----------------+---------+-------+ | eo_order_parent | repeats | total | +-----------------+---------+-------+ | 1 | 2 | 5 | | 2 | 1 | 3 | +-----------------+---------+-------+ 2 rows in set (0.00 sec) 

Then 2/5 is your 40%, and 1/3 is 33%.

Answer 2

The following query calculates the number of repeat customers and the total number of customers in the restaurant

 SELECT u.r_id, u.r_name, SUM(u.no_orders > 1) AS repeats, SUM(u.no_orders) AS orders, COUNT(u.eo_email) AS customers FROM ( SELECT r.*, o.eo_email, COUNT(o.eo_id) AS no_orders FROM lf_restaurants r LEFT JOIN ecom_orders o ON o.eo_order_parent = r.r_id GROUP BY o.eo_email ) u GROUP BY r.r_id; 

The subquery first calculates the number of orders for a client / restaurant pair. An external request calculates from this the number of customers, the number of repeat customers and the total number of customers in the restaurant. You can also calculate the percentage (but this does not need to be done in the query).