SQL Server – Group by and Group by Rollup

In this article, we will see Group By and Group By Rollup in SQL Server.

Group By

The GROUP BY clause in the SQL server groups rows with the same values into summary rows. Group By is used with aggregate functions. Some of the aggregate functions are Count(), Sum(), Max(), Min(), AVG()

Group By Clause Syntax

SELECT column1, column2, column3
FROM Table_Name
WHERE condition
GROUP BY column1, column2, column3
ORDER BY Column_Name(s);

Below is the sample table data.

For the above sample data, write a SQL Server query to get the Sum of Total tourists by Country.

select Country,  sum(NoOfTourist) as TotalTourists from 
CountryTourists
group by Country

Below is the Output

Next, write a SQL Server query to get the Sum of Total tourists Year wise.

select Year,  sum(NoOfTourist) as TotalTourists from 
CountryTourists
group by Year

Output

Group By Rollup

It is the subclass of the GROUP BY clause that creates summary reports and performs an aggregate operation on multiple levels of a hierarchy.

Group By Rollup Syntax

SELECT column1, Column2,  
aggregate_function (columnX)  
FROM TableName  
GROUP BY ROLLUP (column1, Column2) 

or

SELECT column1, Column2,  
aggregate_function (columnX)  
FROM TableName  
GROUP BY column1, Column2 with ROLLUP

ROLLUP assumes a hierarchy among the dimension columns when generating the grouping sets and only generates grouping sets based on this hierarchy. For reporting purposes, the ROLLUP is frequently used to generate subtotals and totals.

For the above sample data, Write a SQL Query to subtotal by Country and Year and then Grand Total at the last.

Rollup by Country and Year

select Country, Year,  sum(NoOfTourist) as TotalTourists from 
CountryTourists
group by rollup(Country,Year)

Output

If we change the order of the columns defined in the GROUP BY clause, it guarantees that we will receive a different response. Look at the query below:

select Country, Year,  sum(NoOfTourist) as TotalTourists from 
CountryTourists
group by rollup(Year,Country)

Output

Summary

The analytical system analyzes data by aggregating it. Data typically use Group By clause with aggregate functions. ROLLUP, on the other hand, can be used to return various aggregate values. The aggregation will be provided by ROLLUP in the specified column order.