Rollup in SQL Server — Transcript

Full transcript

1. 0:00this is part 102 of SQL Server tutorial
2. 0:03in this video we'll discuss rollup and
3. 0:05SQL Server rollup is used to do
4. 0:08aggregate operation on multiple levels
5. 0:11in a hierarchy let's understand this
6. 0:13with an example we have this employees
7. 0:15table here notice we have got employees
8. 0:18in three different countries us UK and
9. 0:21India now based on these employees table
10. 0:24we want to write a query that's going to
11. 0:26retrieve salaries grouped by country
12. 0:29along with grand total so the result of
13. 0:32our query should be like this notice for
14. 0:34Indian employees we are paying 12,000
15. 0:36for UK employees 17,000 us employees
16. 0:4022,500 and then we have the grand total
17. 0:43and if you look at the grand total Row
18. 0:45the country column is null that's
19. 0:46because for Grand totals we are not
20. 0:49grouping by any specific column and then
21. 0:52we have the grand total there are
22. 0:53several ways to produce this result but
23. 0:56the easiest and best way is by using
24. 0:58rollup with groupi So within our result
25. 1:01set we want two columns the country and
26. 1:04total salary so we are selecting country
27. 1:07column from employees table and then we
28. 1:09are applying the sum aggregate function
29. 1:11on the salary column and we giving that
30. 1:14an alas total salary from employees
31. 1:17Group by and we're using roll up so we
32. 1:20want to roll up on the country column so
33. 1:23that's going to give us subtotal as well
34. 1:25I have this exact same query already
35. 1:27typed so when we execute this we get the
36. 1:30salaries grouped by country as well as
37. 1:33the grand total now we discuss that
38. 1:36there are other ways of doing this as
39. 1:38well now we can rewrite this group by
40. 1:40query in a slightly different fashion so
41. 1:43I can also write this query like this
42. 1:45group
43. 1:47by country column and I can use with
44. 1:51rollup so now when we execute these two
45. 1:54queries we should get exactly the same
46. 1:56result now we can also achieve this by
47. 2:00using Union all with groupi so let's
48. 2:03make a copy of
49. 2:06this so now when I remove this with rule
50. 2:10up from this query and when we execute
51. 2:13this we are going to get the salaries
52. 2:15grouped only by country now if we want
53. 2:19the grand total I can write another
54. 2:21query so let's make a copy of this
55. 2:24one now within grand total the country
56. 2:27column is going to be null so let's
57. 2:30select null sum of salary as total
58. 2:33salary from employees and for grand
59. 2:35total we are not going to group on any
60. 2:37specific column so I'm going to remove
61. 2:39Group by so when we execute this query
62. 2:42we are going to get the grand total now
63. 2:44we will apply the union all
64. 2:47operator
65. 2:49between the group buy and our grand
66. 2:52total
67. 2:53query so when we execute this we get
68. 2:56exactly the same
69. 2:57result now we can also use grouping sets
70. 3:00to achieve exactly the same thing so
71. 3:03let's again make a copy of this
72. 3:08query so we want the same two columns
73. 3:11from employees table Group by now we
74. 3:15want to use grouping sets and then we
75. 3:18Define our groups so we want to group by
76. 3:23country so when we execute just this
77. 3:25grouping sets query we're going to get
78. 3:27salaries grouped only by country notice
79. 3:30we don't have grand total now to get
80. 3:33grand
81. 3:35total we are going to use empty
82. 3:37parentheses so this is going to specify
83. 3:39that we want the grand total so now when
84. 3:41we execute this we should get salaries
85. 3:43grouped by country plus the grand
86. 3:47total so there is several ways of
87. 3:49achieving the same result but the
88. 3:51easiest and best way is by using rollup
89. 3:54with
90. 3:56groupi now let's look at another example
91. 4:00so now we want to group salaries by
92. 4:03country and then by gender and we also
93. 4:06want subtotals at Country level as well
94. 4:10as the grand total so if you look at the
95. 4:12result set right here notice for Indian
96. 4:14female employees we are paying 4,000 uh
97. 4:17for Indian male employees 8,000 and for
98. 4:19all Indian employees so that's the
99. 4:21subtotal for India country so we're
100. 4:24paying 12,000 similarly for UK female
101. 4:27employees 5,000 UK male employees 12,000
102. 4:30when we add them together 177,000 so for
103. 4:32all UK employees it's 177,000 so we have
104. 4:35the subtotals at Country level same is
105. 4:38the case for USA as well and then in the
106. 4:42end we also have the grand total if you
107. 4:44look at the subtotal rows notice gender
108. 4:47column is
109. 4:48null whereas if you look at the grand
110. 4:51total row both country and gender column
111. 4:53is null because for grand total we are
112. 4:55not grouping on any specific column so
113. 4:59again to achieve this result there are
114. 5:01several ways of doing it but the best
115. 5:03and easiest way is by using roll up with
116. 5:06group I so this query is very identical
117. 5:09to the previous query the only
118. 5:11difference is in the select list so
119. 5:13within our result set we want the gender
120. 5:14column as well so along with the country
121. 5:16column we included the gender column and
122. 5:19then in our to our rollup function along
123. 5:21with country we're also passing the
124. 5:24gender column those are the only two
125. 5:26differences okay so let's quickly look
126. 5:28at that in action So within our select
127. 5:31list we want the gender
128. 5:34column and we also want to roll
129. 5:37up on gender column so when we execute
130. 5:40this we should get the salaries grouped
131. 5:43by country and the subtotal for country
132. 5:47and then basically the grand total as
133. 5:49well very easy now we can also rewrite
134. 5:53this group by query like this so within
135. 5:57our result set we want
136. 6:00gender and here we will say Group by
137. 6:03country gender with rollup again you
138. 6:06know when we execute these two qu is the
139. 6:08same results so if you look at the
140. 6:09messages look at that 10 rows 10 rows
141. 6:12and the output is exactly the same
142. 6:15thing all right now let's see how to do
143. 6:20the same thing using Union all so the
144. 6:22first query here you know this is just
145. 6:25going to produce total salaries grouped
146. 6:27by country but within our result set we
147. 6:30also want the gender column so let's
148. 6:32specify the gender column right
149. 6:36here and then we want to group
150. 6:39by gender as well so when we execute
151. 6:42this we should get the salaries grouped
152. 6:44by country and by agenda now we want the
153. 6:49salaries grouped by country that is the
154. 6:51country subtotals so in this case we are
155. 6:54going to select
156. 6:56country gender is going to be null and
157. 6:59then of salary from employees and then
158. 7:02we're going to
159. 7:03use Group by just country so this one is
160. 7:08going to give us salaries grouped just
161. 7:11by country so these are like country
162. 7:14subtotals and then finally we want the
163. 7:16grand total and getting grand total is
164. 7:18very easy let's use another Union all
165. 7:21operator and let's make a copy of
166. 7:24this now when we want the grand total we
167. 7:27are not going to group by any specific
168. 7:29column so we will not have the group by
169. 7:32country is going to be null gender is
170. 7:35going to be null and we get the sum of
171. 7:37salary so now when we execute these
172. 7:41three queries we should get the same
173. 7:43result but one thing to keep in mind
174. 7:45here is the order of the Ross in the
175. 7:47result set is not the same to control
176. 7:50the order you can use order by
177. 7:56Clause we can also use the grouping sets
178. 7:58to do the same thing so at the moment
179. 8:00when we execute this we are getting only
180. 8:02the salaries grouped by country and then
181. 8:05the grand total now first of all within
182. 8:08our result set we want gender as well so
183. 8:10within our select Clause I'm going to
184. 8:12specify gender column and then we want
185. 8:15to first group by country and gender
186. 8:19okay and then we want to
187. 8:21group just by country so that's going to
188. 8:24give us the sub totals for country and
189. 8:27then we want the grand total so when we
190. 8:30execute this again the result should be
191. 8:32identical look at that we have the
192. 8:34salaries grouped by country and agenda
193. 8:37then the country subtotal similarly for
194. 8:39UK and USA and finally the grand
195. 8:45total so we have those queries here
196. 8:47using rule up Clause with Group by using
197. 8:50Union all with Group by and finally
198. 8:53using grouping
199. 8:55sets thank you for listening and have a
200. 8:58great day