SQL GROUP BY statement, sqlgroupby statement

SQL GROUP BY statement, sqlgroupby statement

A group by statement is often required for Aggregate functions (such as SUM.

Group by statement

The group by statement is used in combination with the aggregate function to GROUP result sets based on one or more columns.

SQL GROUP BY syntax

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name

SQL GROUP BY instance

We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now we want to find the total amount (total order) for each customer ).

We want to use the group by statement to combine the customer.

We use the following SQL statement:

SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY Customer

The result set is similar to the following:

Customer	SUM (OrderPrice)
Bush	2000
Carter	1700
Adams	2000

 

Great, right?

Let's take a look at what will happen if group by is omitted:

SELECT Customer,SUM(OrderPrice) FROM Orders

The result set is similar to the following:

Customer	SUM (OrderPrice)
Bush	5700
Carter	5700
Bush	5700
Bush	5700
Adams	5700
Carter	5700

The above result set is not what we need.

So why can't I use the preceding SELECT statement? The preceding SELECT statement specifies two columns (Customer and SUM (OrderPrice )). "SUM (OrderPrice)" returns a separate value (total of the "OrderPrice" column ), "Customer" returns six values (each value corresponds to each row in the "Orders" table ). Therefore, we cannot get the correct results. However, you can see that the group by statement solves this problem.

Group by more than one column

You can also apply the group by statement to more than one column, as shown in the following figure:

SELECT Customer,OrderDate,SUM(OrderPrice) FROM OrdersGROUP BY Customer,OrderDate