Group by clause:
-> By using this clause we can be able to show the output in groups.
-> If we place agregate functions in groupby clause, we can display the output in a summarized fashion for each group.
select column_name_used_in_group_by_list,agregatefunction(col2),agregatefunction(col3),......from tablename Group by list_of_columnsorder bylist_of_columns.
select dept,avg(salary) as 'avgsal' from employees
Select min(salary),sum(salary) from employees group by dept,year(doj)
Using more than one column with Group by:
Consider the below ORDERS table,
SELECT customer,OrederDate,SUM(orederprice)FROM Oreders GROUP BY customer,OrederDAte having sum(orderprice)>700
Summarizing Data Using Cube,Rollup,Compute and ComputeBy:
Group by with Cube:
Consider the below given Inventory table,
By using Cube operator we can ble to display the data in a multi dimensional format.
Display how many blue tables,red tables,total tables,total blue items,toatal red items,total blue chairs,total red chairs,total chairs?
SELECT item,color,SUM(Quantity)AS qtysum FROM inventory GROUPBY item, color WITH CUBE
-> The cube operation generated rows for possible combinations of values from both item and color.
For Example, not only does cube report all possible combinations of color values combined with the item value chair(Red,Blue,
-> It also reports all possible combinations of item values combined with the color value Red
(chair,table, and chair+table).
-> For each value in the columns on the columns on the right in the GROUPBY clause, the ROLLUP
operation does not report all possible combinations of values from the column, or columns, on the left.
Foe example, ROLLUP does not report all the possible combinations of item values for each color value.
-> You will not observe that there is no difference between cube and rollup when you use only one column in the groupby clause.
-> You will see the actual difference between cube and rollup when you use multiple columns in the groupby clause along with cube
Compute and Compute By clause
-> Compute and Compute By clauses are provided for backword compatibility.
-> Instead of using compute try to use SQLserver analysis services provided with sqlserver2005.
-> instead of using compute by use Rollup operator.
-> By using compute clause we can produce actual result set along with another result set containing
-> Compute will produce the multiple result sets.
You cannot apply as key word on the computed columns.By default columns names will be produced
with the name as aggregate function.
Consider the below given 'productsales' table
Select * from productsales computesum(originalprice),sum(qty),sum(discount)
-> When we use compute by instead of compute it will produce multiple result sets.
We need to use column name/s in the by clause which are used in the Order By clause only. And we cannot give alias names
to the column names given in the compute and computeby clauses.
Select * from productsales order by p.pname compute sum(s.originalprice),sum(s.qty),sum(s.discount) by p.pname