Summary Row in Sql Result set

Summary Row in Sql Result set

If are performing some kind of aggregation function on a set of fields in sql query and we also want an additional record for summary for eg. Grand Total, Total Avg etc.

In this case we have an extension available for Group by Clause in sql server

                                       "With Rollup"

so if we are calculating avg of order per month than we can also be able to get Total avg.

Select datename(month,orderdate) as monthOfOrder

, avg(price) as avgPriceOfMonth

from orderPrice

group by datename(month,orderdate)

order by datename(month,orderdate)

it will produce simple result avg of each month.

Select case when (Grouping( datename(month,orderdate))=1) Then 'Total Avg'

Else  datename(month,orderdate) 

End as monthOfOrder

, avg(price) as avgPriceOfMonth

from orderPrice

group by datename(month,orderdate) with Rollup

order by datename(month,orderdate)

In this case we will get one additional Row with summary of all Months.


Happy Living.....
Happy Concepts.....
Happy Programming....