Grouping Data or Summary Data in SQL Server statement – part 1

Sql server provides various statements that help us to generate Summary Data for reporting in Sql server Database like Aggregation functions ( SUM, AVERAGE,..) ,GROUP BY , GROUPING SETS , WITH ROLLUP ,WITH CUBE and GROUPING functions :
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns
Let's see these Examples on GROUP BY clause:



First result set demonstrated how we can generate subtotal based on ProductSubcategoryId (we can group by more than one column as same way by adding this column to SELECT clause and GROUP BY clause) and Second result set demonstrated how we can generate total result.
Now let's work With Rollup and Cube (introduced with SQL SERVER 2005):



… Last row



The above result set demonstrated how WITH ROLLUP generated to us subtotal count for each ProductSubcategoryID column.
Notice the last row also show the Total Count for all ProductSubcategoryID subtotal with NULL of ProductSubcategoryID.
Let's see another example by grouping by two columns:



….Last row



WITH CUBE (work with more than one column) show same Summary Data with different way. Let's see this example WITH CUBE:



…Last rows