mysql求每个订单的平均价_MySQL – 选择所有客户和每个客户的总订单和总价值

SELECT a.ID,

a.Name,

COUNT(b.Customer) totalOrders,

SUM(b.value) total_value

FROM Customers a

LEFT JOIN Orders b

ON a.ID = b.Customer

GROUP BY a.ID,

a.Name

要么

SELECT a.ID,

a.Name,

COUNT(b.Customer) totalOrders,

COALESCE(SUM(b.value), 0) total_value

FROM Customers a

LEFT JOIN Orders b

ON a.ID = b.Customer

GROUP BY a.ID,

a.Name

要进一步了解联接,请访问以下链接：

结果,

╔════╦═════════════╦═════════════╦═════════════╗

║ ID ║ NAME ║ TOTALORDERS ║ TOTAL_VALUE ║

╠════╬═════════════╬═════════════╬═════════════╣

║ 1 ║ John Smith ║ 4 ║ 82 ║

║ 2 ║ Jim Jimmers ║ 2 ║ 45 ║

╚════╩═════════════╩═════════════╩═════════════╝