The ORDER BY clause sorts query results by one or multiple attribute columns in ascending or descending order. The query results are sorted in ascending order by default.
Syntax
GROUP BY supports grouping by a single field or multiple fields. You can also use the WHERE clause to filter data before grouping, use the HAVING clause to filter data after grouping, and use the ORDER BY clause to sort data after grouping. Syntax for the GROUP BY clause:
SELECT select_list FROM table_list
[WHERE query_condition]
GROUP BY group_by_expression
[HAVING group_condition];
select_list:
column_name, group_function,...
Parameters in the syntax:
group_function: the aggregate function.group_by_expression: the group expression. Separate multiple expressions by commas (,).group_condition: specifies to filter data after it is grouped.
The following table lists frequently-used aggregate functions in GROUP BY queries.
| Aggregate functions | Description |
|---|---|
| MAX() | Queries the maximum value of the specified column. |
| MIN() | Queries the minimum value of the specified column. |
| COUNT() | Returns the number of rows in the query result. |
| SUM() | Returns the sum of the specified column. |
| AVG() | Returns the average value of the data in the specified column. |
Examples
Create a table named fruit_order and insert proper data.
CREATE TABLE fruit_order(
order_id Number(10,2),
user_id Number(10,2),
user_name VARCHAR2(16),
fruit_price Number(10,2),
order_year Date,
PRIMARY KEY (order_id)
);
INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
(1,1011,'Zhang San',13.11,Date'2019-01-01'),
(4,1011,'Zhang San',22.21,Date'2020-01-01'),
(6,1011,'Zhang San',58.83,Date'2020-02-02'),
(2,1022,'Li Si',23.34,Date'2019-02-02'),
(3,1022,'Li Si',12.22,Date'2019-03-03'),
(7,1022,'Li Si',14.66,Date'2021-03-03'),
(8,1022,'Li Si',34.44,Date'2021-04-04'),
(5,1033,'Wang Wu',51.55,Date'2020-05-05'),
(9,1033,'Wang Wu',63.66,Date'2021-06-06');
GROUP BY query based on a single field
Query the number of orders placed by each customer and print the customer ID and the number of orders.
obclient> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order GROUP BY user_id;
+------------+-----------------+
| Customer ID| Number of orders|
+------------+-----------------+
| 1011 | 3 |
| 1022 | 4 |
| 1033 | 2 |
+------------+-----------------+
3 rows in set
GROUP BY query based on multiple fields
Query the number of orders placed by each customer each year and print the customer ID, the year of order placement, and the number of orders.
obclient> SELECT user_id Customer ID, order_year Year of order placement, COUNT(order_id) Number of orders FROM fruit_order GROUP BY user_id,order_year;
+------------+------------------------+-----------------+
| Customer ID| Year of order placement| Number of orders|
+------------+------------------------+-----------------+
| 1011 | 01-JAN-19 | 1 |
| 1011 | 01-JAN-20 | 1 |
| 1011 | 02-FEB-20 | 1 |
| 1022 | 02-FEB-19 | 1 |
| 1022 | 03-MAR-19 | 1 |
| 1022 | 03-MAR-21 | 1 |
| 1022 | 04-APR-21 | 1 |
| 1033 | 05-MAY-20 | 1 |
| 1033 | 06-JUN-21 | 1 |
+------------+------------------------+-----------------+
9 rows in set
Filter data before grouping
Query the number of orders placed by each customer in 2020 and print the customer ID and the number of orders.
obclient> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order t WHERE t.order_year = '01-JAN-20' GROUP BY user_id;
+------------+-----------------+
| Customer ID| Number of orders|
+------------+-----------------+
| 1011 | 1 |
+------------+-----------------+
1 row in set
Filter data after grouping
Query customers who placed one or more orders in 2019 and print the customer IDs and the numbers of orders.
obclient> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order t WHERE t.order_year = '01-JAN-19' GROUP BY user_id HAVING COUNT(order_id)>=1;
+------------+-----------------+
| Customer ID| Number of orders|
+------------+-----------------+
| 1011 | 1 |
+------------+-----------------+
1 row in set
Sort data after grouping
Query the maximum order amount of each customer and print the customer IDs and maximum order amounts in descending order of maximum order amount.
obclient> SELECT user_id Customer ID, MAX(fruit_price) Maximum order amount FROM fruit_order t GROUP BY user_id ORDER BY Maximum order amount DESC;
+------------+---------------------+
| Customer ID| Maximum order amount|
+------------+---------------------+
| 1033 | 63.66 |
| 1011 | 58.83 |
| 1022 | 34.44 |
+------------+---------------------+
3 rows in set