Rollup in SQL Server

this is part 1 0 2 of sequel server tutorial in this video we'll discuss roll up in sequel server role app is used to do aggregate operation on multiple levels in a hierarchy let us understand this with an example we have this employee's table here notice we have got employees in three different countries US UK and India now based on these employees table we want to write a query that's going to retrieve salaries grouped by country along with grand total so the result of our query should be like this notice for Indian employees we are paying twelve thousand for UK employees seventeen thousand u.s. employees twenty-two thousand five hundred and then we have the grand total and if you look at the grand total row the country column is now that's because for grand totals we are not grouping by any specific column and then we have the grand total there are several ways to produce this result but the easiest and best way is by using roll up with group I so within our result set we want two columns the country and total salary so we are selecting country column from employees table and then we are applying this sum aggregate function on the salary column and we are given that an aleeah's total salary from employees grew by and we are using roll-up so we want to roll up on the country column so that's going to give us subtotal as well I have this exact same query already type so when we execute this we get the salaries grouped by country as well as the grand total now we discuss that there are other ways of doing this as well now we can rewrite this group by query in a slightly different fashion so I can also write this query like this group by country column and I can use width roll-up so now when we execute these two queries we should get exactly the same result now we can also achieve this by using Union all with group I so let's make a copy of this so now when I remove this with roll-up from this query and when we execute we are going to get the salaries grouped only by country now if we want the grand total I can write another query so let's make a copy of this one now within grand total the country column is going to be null so let's select null sum of salary as total salary from employees and for grand total we are not going to group on any specific column so I'm going to remove group I so when we execute this query we are going to get the grand total now we will apply the Union all operator between the group I and our grand total query so when we execute this we get exactly the same result now we can also use grouping sets to achieve exactly the same thing so let's again make a copy of this query so we want the same two columns from employee table grew by now we want to use grouping sets and then we define other groups so we want to group by country so when we execute just this grouping sets query we are going to get salaries grouped only by country notice we don't have grand total now to get grand total we are going to use empty parentheses so this is going to specify that we want the grand total so now when we execute this we should get salaries grouped by country plus the grand total so there is several ways of achieving the same result but the easiest and best way is by using roll-up with group I now let's look at another example so now we want to group salaries by country and then by gender and we also want subtotals at country level as well as the grand total so if you look at the result set right here notice for Indian female employees we are paying four thousand for Indian male employees eight thousand and for all Indian employees so that's the subtotal for India Country so we are paying 12,000 similarly for UK female employees 5,000 UK male employees 12,000 when we add them together 17,000 so for all UK employees it's 17,000 so we have the subtotals at country level same is the case for USA as well and then in the end we also have the grand total if you look at the subtotal rows notice gender column is now whereas if you look at the grand total row both country and gender column is not because the grand total we are not grouping on any specific column so again to achieve this result there are several ways of doing it but the best and easiest way is by using roll-up with group I so this query is very identical to the previous query the only difference is in the Select list so within our result set we want the gender column as well so along with the country column we included the gender column and then in our to a rollup function along with country we are also passing the gender column those are the only two differences okay so let's quickly look at that in action so within our select list we want the gender column and we also want to roll up on gender column so when we execute this we should get this salaries grouped by country and the subtotal for country and then basically the grand total as well very easy now we can also rewrite this group by query like this so with a no result set we want gender and here we will say group by country gender with roll-up again you know when we execute these two queries the same result so if you look at the messages look at that tendrils chain rules and the output is exactly the same thing all right now let's see how to do the same thing using Union all so the first query here you know this is just going to produce total salaries grouped by country but within our result set we also want the gender column so let's specify the gender column right here and then we want to group by gender as well so when we execute this we should get the salaries grouped by country and by gender now we want the salaries grouped by country that is the country subtotals so in this case we are going to select country gender is going to be now and then sum of salary from employees and then we are going to use group by just country so this one is going to give us salaries grouped just by country so these are like country subtotals and then finally we want the grand total and getting grand total is very easy let's use another Union all operator and let's make a copy of this now when we want the grand total we are not going to group by any specific column so we will not have the group by country is going to be null gender is going to be now and we get the sum of salary so now when we execute these three queries we should get the same result but one thing to keep in mind here is the order of the rows in the result set is not the same to control the order you can use order by Clause we can also use the grouping sets to do the same thing so at the moment when we execute this we are getting only the salaries grouped by country and then the grand total now first of all with no results that we want gender as well so within our select clause I'm going to specify gender column and then we want to first group by country and gender okay and then we want to group just by country so that's going to give us the subtotals for country and then we want the grand total so when we execute this again the result should be identical look at that we have the salaries grouped by country and gender then the country subtotal similarly for UK and USA and finally the grand total so we have those queries here using roll-up claws with glue by using Union all that grew by and finally using grouping sets thank you for listening and have a great day

Channel: kudvenkat

Views: 92,164

Rating: 4.8859062 out of 5

Keywords: sql server group by rollup, sql server 2008 group by rollup, group by with rollup example, sql rollup example, sql server rollup example, sql server 2008 rollup examples rollup clause in sql server, rollup function in sql server

Id: HLTdfCtfIJs

Length: 9min 0sec (540 seconds)

Published: Sun Sep 20 2015