Home Database Mysql Tutorial mysql data grouping and sorting and SELECT clause order

mysql data grouping and sorting and SELECT clause order

May 09, 2017 pm 01:50 PM

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.

mysql data grouping and sorting and SELECT clause order

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;
Copy after login
Output:

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

mysql data grouping and sorting and SELECT clause orderInput:

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

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 mysql data grouping and sorting and SELECT clause 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 and sorting and SELECT clause order

mysql data grouping and sorting and SELECT clause order

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to sort photos by date taken in Windows 11/10 How to sort photos by date taken in Windows 11/10 Feb 19, 2024 pm 08:45 PM

This article will introduce how to sort pictures according to shooting date in Windows 11/10, and also discuss what to do if Windows does not sort pictures by date. In Windows systems, organizing photos properly is crucial to making it easy to find image files. Users can manage folders containing photos based on different sorting methods such as date, size, and name. In addition, you can set ascending or descending order as needed to organize files more flexibly. How to Sort Photos by Date Taken in Windows 11/10 To sort photos by date taken in Windows, follow these steps: Open Pictures, Desktop, or any folder where you place photos In the Ribbon menu, click

How to sort emails by sender, subject, date, category, size in Outlook How to sort emails by sender, subject, date, category, size in Outlook Feb 19, 2024 am 10:48 AM

Outlook offers many settings and features to help you manage your work more efficiently. One of them is the sorting option that allows you to categorize your emails according to your needs. In this tutorial, we will learn how to use Outlook's sorting feature to organize emails based on criteria such as sender, subject, date, category, or size. This will make it easier for you to process and find important information, making you more productive. Microsoft Outlook is a powerful application that makes it easy to centrally manage your email and calendar schedules. You can easily send, receive, and organize email, while built-in calendar functionality makes it easy to keep track of your upcoming events and appointments. How to be in Outloo

PHP development: How to implement table data sorting and paging functions PHP development: How to implement table data sorting and paging functions Sep 20, 2023 am 11:28 AM

PHP development: How to implement table data sorting and paging functions In web development, processing large amounts of data is a common task. For tables that need to display a large amount of data, it is usually necessary to implement data sorting and paging functions to provide a good user experience and optimize system performance. This article will introduce how to use PHP to implement the sorting and paging functions of table data, and give specific code examples. The sorting function implements the sorting function in the table, allowing users to sort in ascending or descending order according to different fields. The following is an implementation form

How does Arrays.sort() method in Java sort arrays by custom comparator? How does Arrays.sort() method in Java sort arrays by custom comparator? Nov 18, 2023 am 11:36 AM

How does Arrays.sort() method in Java sort arrays by custom comparator? In Java, the Arrays.sort() method is a very useful method for sorting arrays. By default, this method sorts in ascending order. But sometimes, we need to sort the array according to our own defined rules. At this time, you need to use a custom comparator (Comparator). A custom comparator is a class that implements the Comparator interface.

How to sort WPS scores How to sort WPS scores Mar 20, 2024 am 11:28 AM

In our work, we often use wps software. There are many ways to process data in wps software, and the functions are also very powerful. We often use functions to find averages, summaries, etc. It can be said that as long as The methods that can be used for statistical data have been prepared for everyone in the WPS software library. Below we will introduce the steps of how to sort the scores in WPS. After reading this, you can learn from the experience. 1. First open the table that needs to be ranked. As shown below. 2. Then enter the formula =rank(B2, B2: B5, 0), and be sure to enter 0. As shown below. 3. After entering the formula, press the F4 key on the computer keyboard. This step is to change the relative reference into an absolute reference.

Asynchronous processing method of Select Channels Go concurrent programming using golang Asynchronous processing method of Select Channels Go concurrent programming using golang Sep 28, 2023 pm 05:27 PM

Asynchronous processing method of SelectChannelsGo concurrent programming using golang Introduction: Concurrent programming is an important area in modern software development, which can effectively improve the performance and responsiveness of applications. In the Go language, concurrent programming can be implemented simply and efficiently using Channels and Select statements. This article will introduce how to use golang for asynchronous processing methods of SelectChannelsGo concurrent programming, and provide specific

How to sort in excel How to sort in excel Mar 05, 2024 pm 04:12 PM

Sorting methods in excel: 1. Single column sorting; 2. Multiple column sorting; 3. Custom sorting. Detailed introduction: 1. Single-column sorting is the most common sorting method. It sorts according to a selected column; 2. Multi-column sorting refers to sorting data in multiple columns, usually sorting according to a certain column first. On the basis of, sort by another column; 3. Custom sorting, allowing users to define the sort order according to their own needs.

How to sort WPS tables to facilitate data statistics How to sort WPS tables to facilitate data statistics Mar 20, 2024 pm 04:31 PM

WPS is a very complete office software, including text editing, data tables, PPT presentations, PDF formats, flow charts and other functions. Among them, the ones we use most are text, tables, and demonstrations, and they are also the ones we are most familiar with. In our study work, we sometimes use WPS tables to make some data statistics. For example, the school will count the scores of each student. If we have to manually sort the scores of so many students, it will be really a headache. In fact, we don’t have to worry, because our WPS table has a sorting function to solve this problem for us. Next, let’s learn how to sort WPS together. Method steps: Step 1: First we need to open the WPS table that needs to be sorted

See all articles