SQL GROUP by statement

Aggregate functions, such as SUM, often require the addition of a GROUP by statement.

GROUP by statement

The GROUP BY statement is used to combine aggregate functions to group result sets based on one or more columns.

SQL GROUP by syntax

 
   
  

  
    
   
SELECT column_name, aggregate_function(column_name)
  
    
   
FROM table_name
  
    
   
WHERE column_name operator value
  
    
   
GROUP BY column_name
 
   
  

SQL GROUP By instance we have the following "Orders" table:

· o_id OrderDate Orderprice Customer
1 2008/12/29 Bush
2 2008/11/23-Carter
3 2008/10/05-Bush
4 2008/09/28 Bush
5 2008/08/06 Adams
6 2008/07/21 Carter

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

We want to use GROUP by statements to group customers.
We use the following SQL statements:

 
   
  

  
    
   
SELECT Customer,SUM(OrderPrice) FROM Orders
  
    
   
GROUP BY Customer
 
   
  

The result set looks like this:
Customer SUM (Orderprice)
Bush 2000
Carter 1700
Adams 2000

It's great, isn't it?

Let's take a look at what happens if you omit GROUP by:

 
   
  

  
    
   
SELECT Customer,SUM(OrderPrice) FROM Orders
 
   
  

The result set looks like this:
Customer SUM (Orderprice)
Bush 5700
Carter 5700
Bush 5700
Bush 5700
Adams 5700
Carter 5700

The result set above is not what we need.

So why not use the SELECT statement above? This is explained as follows: The above SELECT statement specifies two columns (Customer and SUM (Orderprice)). SUM (Orderprice) returns a separate value (total for the "orderprice" column), and "Customer" returns 6 values (each corresponding to each row in the Orders table). Therefore, we do not get the right results. However, you have seen that the GROUP by statement solves this problem.

GROUP by more than one column

We can also apply the GROUP by statement to more than one column, just like this:

 
   
  

  
    
   
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
  
    
   
GROUP BY Customer,OrderDate
 
   
  

From for notes (Wiz)

SQL GROUP by statement