mysql data grouping and sorting and SELECT clause order

mysql grouping and sorting

Although GROUP BY and ORDER BY often accomplish the same job, they are very different. The table below summarizes their differences.

The first difference listed in the table is extremely important. We often find that data grouped using GROUP BY is indeed output in group order. But this is not always the case, and it is not required by the SQL specification. Additionally, users may request that the items be sorted in a different order than grouped. Just because you group the data a certain way (to get a specific grouped aggregate value), doesn't mean you need order the output the same way. An explicit ORDER BY clause should be provided, even if its effect is the same as a GROUP BY clause. Don’t forget ORDER BY: Generally when using the GROUP BY clause, you should also give

an ORDER BY clause. This is the only way to guarantee that the data is sorted correctly. Never don’t rely solely on GROUP BY to sort data. To illustrate the use of GROUP BY and ORDER BY, please look at an example. The SELECT statement below is similar to the previous examples. It retrieves the order number and total order price of orders with a total order price greater than or equal to 50:

Input:

select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50;

Output:

To sort the output by the total order price, you need to add an ORDER BY clause, As shown below:

Input:

select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50 order by ordertotal;

Output:

Analysis: In this example, the GROUP BY clause is used to group the data by order number (order_num column)

so that the SUM(*) function can return Total order price. The HAVING sub-sentence filters the data so that only orders with a total order price greater than or equal to 50 are returned. Finally, use the ORDER

BY clause to sort the output.

SELECT clause order Let’s review the order of clauses in the SELECT statement. Table 13-2 lists the clauses learned so far in the order in which

must be used in a SELECT statement.

【Related recommendations】

mysql data grouping: create grouping

1. mysql data grouping: filter grouping

The above is the detailed content of mysql data grouping and sorting and SELECT clause order. For more information, please follow other related articles on the PHP Chinese website!