mysql uses join with aggregate function

Aggregation functions are used to summarize data. Although all examples of aggregate functions so far have only summarized data from a single table, these functions can also be used with joins. To illustrate this, consider an example. If you want to retrieve all customers and the number of orders placed by each customer, the following code using the COUNT() function can do the job:

Input:

select customers.cust_name,customers.cust_id,count(order_num) as num_ord from customers inner join orders on customers.cust_id = orders.cust_id group by custo,=mers.cust_id;

Output:

# Analysis: This SELECT statement uses INNER JOIN to relate the customers and orders tables to each other. The GROUP BY clause groups the data by customer, so the function call COUNT(orders.order_num) counts the orders for each customer, returning it as num_ord.

Aggregation functions can also be easily used with other joins. Please look at the following example:

Input:

select customers.cust_name,customers.cust_id,count(order_num) as num_ord from customers left outer join orders on customers.cust_id = orders.cust_id group by custo,=mers.cust_id;

Output:

Analysis: This The example uses a left outer join to include all customers, even those who have not placed any orders. The results show that the customer Mouse House is also included, and it has 0 orders.

The above is the detailed content of mysql uses join with aggregate function. For more information, please follow other related articles on the PHP Chinese website!