Menu
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 ║
╚════╩═════════════╩═════════════╩═════════════╝
246
We use the SELECT * FROM table_name command to select all the columns of a given table. In the following example we are selecting all the columns of the employee table. mysql> SELECT * FROM employee; And we get the following output.
To select last two rows, use ORDER BY DESC LIMIT 2.
To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.
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
OR
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
To further gain more knowledge about joins, kindly visit the link below:
Result,
╔════╦═════════════╦═════════════╦═════════════╗
║ ID ║ NAME ║ TOTALORDERS ║ TOTAL_VALUE ║
╠════╬═════════════╬═════════════╬═════════════╣
║ 1 ║ John Smith ║ 4 ║ 82 ║
║ 2 ║ Jim Jimmers ║ 2 ║ 45 ║
╚════╩═════════════╩═════════════╩═════════════╝
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
