'MySQL - Select all customers and each customer's total orders and total value

For each customer, I want to return: id, name, total_orders, total_value

Customers:

╔════╦═════════════╗
║ ID ║    NAME     ║
╠════╬═════════════╣
║  1 ║ John Smith  ║
║  2 ║ Jim Jimmers ║
╚════╩═════════════╝

Orders:

╔═══════╦══════════╦═══════╗
║  ID   ║ CUSTOMER ║ VALUE ║
╠═══════╬══════════╬═══════╣
║ 34656 ║        1 ║    20 ║
║ 37345 ║        2 ║    25 ║
║ 38220 ║        1 ║    15 ║
║ 39496 ║        1 ║    38 ║
║ 41752 ║        1 ║     9 ║
║ 43734 ║        2 ║    20 ║
╚═══════╩══════════╩═══════╝

How do I select a result like:

╔════╦═════════════╦═════════════╦═════════════╗
║ ID ║    NAME     ║ TOTALORDERS ║ TOTAL_VALUE ║
╠════╬═════════════╬═════════════╬═════════════╣
║  1 ║ John Smith  ║           4 ║          82 ║
║  2 ║ Jim Jimmers ║           2 ║          45 ║
╚════╩═════════════╩═════════════╩═════════════╝
mysqlsqlselectjoin


Solution 1:[1]

This query should give your deriderated output:

SELECT c.id, c.name, count(*) AS total_orders, sum(o.value) AS total_value
FROM Customers AS c
     LEFT JOIN Orders AS o ON c.id = o.customer
GROUP BY c.id

Solution 2:[2]

Use a inner join and then a sum by grouping user id.

SQL join is used to merge tables by an equality point, you should give a look at this http://dev.mysql.com/doc/refman/5.0/en/join.html

sorry i don't have a mysql server right now bu is something like this:

select id, name, sum(value) from (select customers.id as id, customers.name as name, orders.value as value from customers inner join orders on customers.id = orders.customer) group by id

Almost sure is not quite right but somewhere close.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 araknoid
Solution 2 TtheT

Related Questions