How is the “group by” clause used in SQL?

In SQL, GROUP BY is used to group the result set by one or more columns. It is often used with aggregate functions (such as SUM, COUNT, AVG, etc.) to apply aggregate operations to each group.

The basic syntax of the GROUP BY statement is as follows:

SELECT 列1, 列2, ..., 列n
FROM 表名
GROUP BY 列1, 列2, ..., 列n;

For example, let’s say we have an “Orders” table with columns like “OrderID”, “CustomerID”, “OrderDate”, and “TotalAmount”. If we want to group the orders by the “CustomerID” column and calculate the total amount of orders for each customer, we can use the following query:

SELECT CustomerID, SUM(TotalAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID;

This query will retrieve the total order amount for each customer ID.

Please note that the columns specified in the GROUP BY clause must also appear in the SELECT clause (unless they are being used in an aggregate function). Furthermore, if there are no aggregate functions in the SELECT clause, only columns specified in the GROUP BY clause can be selected.