MySQL

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 ║
╚════╩═════════════╩═════════════╩═════════════╝

How do I select all values in a column in MySQL?

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.

How do I select multiple rows in MySQL?

To select last two rows, use ORDER BY DESC LIMIT 2.

How do I select the number of rows in MySQL?

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

SQLFiddle Demo

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:

Visual Representation of SQL Joins

Result,

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

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

Tags:

sql

join

select

BadHorsie

People also ask

1 Answers

John Woo

Recent Activity

Donate For Us