@shpro654 חילקתי את הבעיה ל-3 בעיות יותר קלות, וכתבתי לכל אחת שאילתא נפרדת.
רשימת הרכישות הרצויות מעל 50 ש"ח:
SELECT customers.group_id, shopping.customer_id, Sum(IIf([purchase_amount]>50,1,0)) AS valid_puchase_count
FROM customers INNER JOIN shopping ON customers.id = shopping.customer_id
GROUP BY customers.group_id, shopping.customer_id
HAVING (((Sum(IIf([purchase_amount]>50,1,0)))>0));
ואז על ידיה אפשר לקבל את מספר הרכישות הרצויות בכל קבוצה:
SELECT valid_puchases.group_id, Count(valid_puchases.customer_id) AS customers_with_valid_purchases
FROM (
SELECT customers.group_id, shopping.customer_id, Sum(IIf([purchase_amount]>50,1,0)) AS valid_puchase_count
FROM customers INNER JOIN shopping ON customers.id = shopping.customer_id
GROUP BY customers.group_id, shopping.customer_id
HAVING (((Sum(IIf([purchase_amount]>50,1,0)))>0))) AS valid_puchases
GROUP BY valid_puchases.group_id;
מספר הלקוחות הכולל בכל קבוצה:
SELECT groups.group_name, groups.id, Count(customers.id) AS customers_in_group
FROM groups LEFT JOIN customers ON groups.id = customers.group_id
GROUP BY groups.group_name, groups.id;
ולבסוף איגדתי את כולן לשאילתא אחת:
SELECT AllCustomers.group_name, AllCustomers.id, AllCustomers.customers_in_group, CustomersWithPurchase.customers_with_valid_purchases, IFNULL(CustomersWithPurchase.customers_with_valid_purchases,0)/(AllCustomers.customers_in_group) AS Per
FROM (
SELECT groups.group_name, groups.id, Count(customers.id) AS customers_in_group
FROM groups LEFT JOIN customers ON groups.id = customers.group_id
GROUP BY groups.group_name, groups.id) AS AllCustomers
LEFT JOIN (
SELECT valid_puchases.group_id, Count(valid_puchases.customer_id) AS customers_with_valid_purchases
FROM (
SELECT customers.group_id, shopping.customer_id, Sum(IIf([purchase_amount]>50,1,0)) AS valid_puchase_count
FROM customers INNER JOIN shopping ON customers.id = shopping.customer_id
GROUP BY customers.group_id, shopping.customer_id
HAVING (((Sum(IIf([purchase_amount]>50,1,0)))>0))) AS valid_puchases
GROUP BY valid_puchases.group_id) AS CustomersWithPurchase
ON AllCustomers.id = CustomersWithPurchase.group_id;
