Wednesday, November 28

SQL- GROUP BY



Group By

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. You can’t have a non-aggregated column and an aggregated column in a column list, unless you include a Group By clause.

Syntax:

  " SELECT column_name, aggregate_function(column_name)
    FROM table_name
    WHERE column_name operator value
    GROUP BY column_name "                // where clause can be

                                             used depending   
                                             on result need

Example:
See the below Item_Order table.
Now applying the below query.

   " SELECT Cust_Name,SUM(Price)as Total_Price FROM Item_Order
     GROUP BY Cust_Name "

Result:
If you execute the query with out Group By, It will throw an error message saying "Column 'Item_Order.Cust_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." 


Group By More Than One Column

Lets see the below query.


  " SELECT Cust_Name, Dat, SUM(Price)as Total_Price FROM   

    Item_Order
    GROUP BY Cust_Name, Dat "

Here Group By is applied on both columns Cust_Name and dat.

Result:




Note:If Rahul order 1 more item on 9/16/2012, Lets say for Price 2000. Then Total_Price will be 17000 for that date and Cust_name.

Click here to know more about Aggregate Functions