@dovid אתה צודק שבשאילתה של הדוגמא שלי זה יכול להסתדר כך
אבל במקורית שאמורה להיות כך:
SELECT
customers.id,
customers.name,
customers.email,
customers.phone,
sum(order_items.qty) as qty,
sum(payments.amount) as payup,
sum(order_items.price - payments.amount) as balance
FROM customers
left join orders on orders.customer_id = customers.id
left join order_items on orders.id = order_items.order_id
LEFT join payments on payments.order_id = orders.id
group by customers.id
זה וודאי לא יהיה טוב מכיוון שיש כאן רשומות שנכפלות והתוצאה של הpayup לא תהיה נכונה, כמובן שגם הbalance.
ואז מה שאני רוצה לעשות זה כך:
SELECT
customers.id,
customers.name,
customers.email,
customers.phone,
sum(order_items.qty) as qty,
sum(order_items.price - (SELECT COALESCE(sum(payments.amount), 0) from payments join orders on orders.id = payments.order_id where orders.customer_id = customers.id)) as balance,
(SELECT sum(payments.amount) from payments where payments.order_id in (GROUP_CONCAT(orders.id))) as payup
FROM customers
left join orders on orders.customer_id = customers.id
left join order_items on order_items.order_id = orders.id
group by customers.id