How to calculate Average services ordered per order with unique reference by user? (MySQL)

Question

I think my question is more about math than MySQL.

Placed order can belongs to one of three possible services (for ex A, B, C). User have for ex. 100 placed orders (for ex. 30 of service A, 40 of service B and 30 of service C). And placed order have Property reference (some orders belongs to the same property reference, some not).

I need to calculate "Average services count ordered per listing (unique property reference)" for one specific user. This value will be from 0 to 3 (for ex. 1.4).

For example if user ordered 3 orders for "Property X" (and does not ordered for other properties) reference and this was orders for service A, service A and service C - average value will be 2 (he ordered 2 services types for the same property reference).

Sample data:

CREATE TABLE IF NOT EXISTS `orders` (
`id` int(6) NOT NULL,
`user_id` int(6) NOT NULL,
`reference` varchar(64) NOT NULL,
`service_type` varchar(16) NOT NULL
);
INSERT INTO `orders` (`id`, `user_id`, `reference`, `service_type`) VALUES
('4', '66', 'Blue house',  'serviceA'),
('2', '66', 'Blue house',  'serviceB'),
('5', '66', 'Blue house',  'serviceB'),
('6', '66', 'Blue house',  'serviceC'),
('1', '66', 'Red house',   'serviceA'),
('3', '66', 'Red house',   'serviceA'),
('7', '66', 'Green house', 'serviceC')

In this example: User ordered 3 unique services for 'Blue house' reference, 1 unique service for 'Red house' and 1 unique service for 'Green house' reference. I need to know average ordered services count per unique listing (not for every listng, average in total - just ONE value for all this data). So we have 3 unique references here for user ( Blue, red, green houses ) and know how many unique services each one reference have assigned. How to get average?

Expected output that I need from calculation: ONE value, something like 1.7 (I don't know real value from this example, because I don't know how to calculate this mathematically). This means that user have ordered 1.7 (from 3 available) services for unique property reference in average during all period within all orders.

I can get user total orders, user total orders with unique property reference without any problem, but I can't understand how I can calculate what I need without doing a lot of requests and math calculations in PHP.

For example getting count of orders with unique order references (3 in my example below):

SELECT COUNT(distinct reference) as total_orders FROM orders WHERE user_id = 66;

But I don't understand what to do next.

Sample data: http://sqlfiddle.com/#!9/dd00d7

Answer 1

If I understand correctly you need two levels of GROUP BY. On the inner level you need number of distinct services per listing (blue house = 3, red house = 1, green = 1). On the outer level you calculate the average of 3, 1, 1:

SELECT userid, AVG(service_type_count) AS avg_service_type_per_listing
FROM (
    SELECT userid, reference, COUNT(DISTINCT service_type) AS service_type_count
    FROM orders
    GROUP BY userid, reference
) AS x
GROUP BY userid

Answer 2

Here's a guess where the subquery identifies the distinct service by user and reference and the outer query works out the average.

drop table if exists t;

CREATE TABLE t (
  `id` int(6) NOT NULL,
  `userid` int(6) NOT NULL,
  `reference` varchar(64) NOT NULL,
  `service_type` varchar(16) NOT NULL
);
INSERT INTO t (`id`, `userid`, `reference`, `service_type`) VALUES
  ('1', '66', 'Red house', 'serviceA'),
  ('2', '66', 'Blue house', 'serviceB'),
  ('3', '66', 'Red house', 'serviceA'),
  ('4', '66', 'Blue house', 'serviceA'),
  ('5', '66', 'Blue house', 'serviceB'),
  ('6', '66', 'Blue house', 'serviceC'),
  ('7', '66', 'Green house', 'serviceC');

select s.userid,
        sum(s.distinctservice_types) distinctservice_types,
        sum(s.distinctservice_types) / 3 avgs
from
(
select userid,reference,count(Service_type) countservice_types,
         count(distinct service_type) distinctservice_types
from t
group by userid,reference
) s
group by s.userid
;

+--------+-----------------------+--------+
| userid | distinctservice_types | avgs   |
+--------+-----------------------+--------+
|     66 |                     5 | 1.6667 |
+--------+-----------------------+--------+
1 row in set (0.00 sec)