Tutorial on creating calculated fields using subqueries in mysql

Using subqueries as calculated fields

Another way to use subqueries is to create calculated fields. Suppose you need to display the total number of orders for each customer in the customers table. Orders are stored in the orders table with corresponding customer IDs.

In order to perform this operation, follow the steps below.

(1) Retrieve the customer list from the customers table.

(2) For each retrieved customer, count the number of orders in the orders table.

As mentioned in the previous two chapters, you can use SELECT COUNT (*) to count the rows in the table, and by providing a WHERE clause to filter for a specific customer ID, you can filter only for that customer Orders are counted. For example, the following code counts orders for customer 10001:

Input:

select count(*) as orders from orders where cust_id = 10001;

To perform a COUNT(*) calculation for each customer, COUNT(*) should be used as a subquery . Please look at the following code:

Input:

select cust_name,cust_state,(select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;

Output:

## Analysis: This SELECT statement applies to each customer in the customers table Customer returns 3 columns: cust_name, cust_state, and orders. orders is a calculated field established by a subquery in parentheses. This subquery is executed once for each customer retrieved. In this example, the subquery was executed 5 times because 5 customers were retrieved.

The WHERE clause in the subquery is slightly different from the WHERE clause used previously because it uses fully qualified column names (first mentioned in Chapter 4). The following statement tells SQL to compare the cust_id in the orders table with the cust_id currently being retrieved from the customers table:

where orders.cust_id = customers.cust_id

Correlated subquery (correlated subquery) A subquery involving the outer query. This type of subquery is called a correlated subquery. This syntax (table name and column name separated by a period) must be used whenever a column name may be ambiguous. why is it like this?

Let’s see what happens if we don’t use fully qualified column names:

Input:

select cust_name,cust_state,(select count(*) from orders where cust_id = cust_id) as orders from customers order by cust_name;

Output:

Analysis: Obviously, the returned results are incorrect (please compare the previous results), so why is this happening? There are two cust_id columns, one in customers and one in orders, that need to be compared to correctly match orders to their corresponding customers. If you do not fully qualify the column name, MySQL will assume that you are comparing the cust_id against itself in the orders table. And SELECT COUNT(*) FROM orders WHERE cust_id = cust_id; always returns the total number of orders in the orders table (because MySQL looks to see if the cust_id of each order matches itself, and of course, they always do).

Although subqueries are extremely useful in constructing this SELECT statement, care must be taken to limit ambiguous column names.

More than one solution As stated earlier in this chapter, although the sample code given here works well, it is not the most efficient way to solve this kind of data retrieval. We will encounter this example again in later chapters.

Build a query by gradually adding subqueries. Testing and debugging queries with subqueries can be tricky, especially as the complexity of these statements increases. The most reliable way to build (and test) a query with subqueries is to do it incrementally, much the same way MySQL handles them. First, build and test the innermost query. Then, build and test the outer query with hard-coded data, and only embed the subquery after confirming that it works. At this point, test it again. Repeat these steps for each query you want to add. Doing this adds only a small amount of time to constructing the query, but saves a lot of time later (finding out why the query is not working) and greatly increases the likelihood that the query will work properly in the first place.

[Related recommendations]

1. What is a mysql subquery? How to filter using subqueries?

2.What are connection and relationship tables in mysql?

3. Why use joins and how to create joins

4.The importance of WHERE clause in MySQL and how to join multiple tables

The above is the detailed content of Tutorial on creating calculated fields using subqueries in mysql. For more information, please follow other related articles on the PHP Chinese website!