First time user?    |

```
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.

Syntax:
select column_name_used_in_group_by_list,agregatefunction(col2),agregatefunction(col3),......from tablename Group by list_of_columnsorder bylist_of_columns.

Ex:
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,

e_idorderdateorderpricecustomer

12008-11-12 00:00:001000Hansen
22008-10-23 00:00:001600Nilsen
32008-09-02 00:00:00700Hansen
42008-09-03 00:00:00300Hansen
52008-08-30 00:00:002000Jensen
62008-10-04 00:00:00100Nilsen
42008-09-03 00:00:00300Hansen
52008-08-30 00:00:002000Jensen
62008-10-04 00:00:00100Nilsen

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,

ItemcolorQuantity

TableBlue124
TableRed223
ChairBlue101
ChairRed210

By using Cube operator we can ble to display the data in a multi dimensional format.

Query: 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

Rollup:
-> 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,
and 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.

Note
-> 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
and rollup.

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
summarised data.

-> Compute will produce the multiple result sets.

Note
You cannot apply as key word on the computed columns.By default columns names will be produced
with the name as aggregate function.

Ex: Consider the below given 'productsales' table

sidqtyoriginalpricetotalpricediscountsaledatepname

110135012001502011-12-23 00:00:00Doveshamp
225200018002002011-01-23 00:00:00Doveshamp
320130010003002010-08-13 00:00:00Doveshamp
4100550049006002008-06-02 00:00:00Doveshamp
550130011002002012-08-02 00:00:00Doveshamp
615375350252011-12-23 00:00:00Doveshamp
725337531002752011-10-07 00:00:00Doveshamp
8200300025005002010-10-29 00:00:00Doveshamp
9150330032001002011-07-29 00:00:00Doveshamp
10100350012003002011-11-15 00:00:00Doveshamp
1110550500502011-02-02 00:00:00Doveshamp
1250110010001002009-08-11 00:00:00Doveshamp
138012009502502010-12-23 00:00:00Doveshamp

Query:
Select * from productsales computesum(originalprice),sum(qty),sum(discount)

ComputeBy:
-> When we use compute by instead of compute it will produce multiple result sets.

Note
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.

Query:
Select * from productsales order by p.pname compute sum(s.originalprice),sum(s.qty),sum(s.discount) by p.pname

```