Grouping Sets in SQL Server — Transcript

Full transcript

1. 0:00this is part 101 of SQL Server tutorial
2. 0:03in this video we'll discuss grouping
3. 0:04sets in SQL Server grouping sets is a
4. 0:07new feature introduced in SQL Server
5. 0:092008 let's understand grouping sets with
6. 0:12an example we'll be using this employees
7. 0:14table in this demo I've already created
8. 0:16this table here is the SQL script to
9. 0:18create and populate it with test data
10. 0:20I'll have the script available on my
11. 0:21blog in case you need it now based on
12. 0:24this employees table we want to write a
13. 0:26query that is going to calculate some of
14. 0:29salary
15. 0:30grouped by country column first and then
16. 0:33by gender column so the result of a
17. 0:35query should be like this so if you look
18. 0:38at the data here notice for female
19. 0:41employees in India we are paying 4,000
20. 0:44as the total salary for UK female
21. 0:46employees we paying 5,000 for us female
22. 0:48employees
23. 0:5012,500 so here the salaries are grouped
24. 0:53by country and then by gender to achieve
25. 0:56this we can write a simple Group by
26. 0:57query as you can see here within the set
27. 1:00we want these three columns country
28. 1:02genda and sum of salary So within our
29. 1:05select list here we have country genda
30. 1:08we are applying the sum aggregate
31. 1:09function on the salary column and we are
32. 1:12giving that column and alas total salary
33. 1:15from employees table we first grouping
34. 1:17by country and then by agenda I have
35. 1:20this group by query already typed so if
36. 1:22we execute this query we should get the
37. 1:25salaries grouped by country and then by
38. 1:27genda so along with this we also want
39. 1:31the sum of salaries grouped just by
40. 1:34country so if you look at the three rows
41. 1:36that are with green background color
42. 1:39notice that for Indian employees we are
43. 1:41paying 12,000 now if you look at India
44. 1:44female employees we are paying 4,000 for
45. 1:46India male employees 8,000 so if you add
46. 1:49those two rows together 4 + 8 is 12,000
47. 1:52so for all Indian employees it 12,000
48. 1:55similarly if you look at UK for UK
49. 1:57female employees it is 5,000 for UK
50. 2:00employees it's 12,000 12 + 5 is 17 so UK
51. 2:04total salary by country is
52. 2:06177,000 now to produce these three rows
53. 2:09that are with green background color you
54. 2:11know we can use another group by query
55. 2:14as you can see here so we want the
56. 2:16country So within the select list we
57. 2:17want country now since we are grouping
58. 2:20here just by country gender is going to
59. 2:23be null so we are selecting null for
60. 2:26gender column and then the sum of salary
61. 2:29from employees and we want to group just
62. 2:31by country and between these two group
63. 2:33by queries we are applying Union all
64. 2:35operator so this query is going to
65. 2:38produce this result set let's quickly
66. 2:40look at that in action so I'm going to
67. 2:42make a copy of this
68. 2:44query and then I'm going to make the
69. 2:46modifications so we want country gender
70. 2:49is going to be null because we are going
71. 2:51to group just by uh country so from our
72. 2:54group by Clause I'm going to remove
73. 2:55jenda and between these two queries we
74. 2:58will apply the union all operator but
75. 3:00before that if we execute just this
76. 3:02query notice that we get the total
77. 3:04salaries grouped only by country whereas
78. 3:07when we execute this query we get the
79. 3:10first six rows that is salaries grouped
80. 3:12by country and then by agenda now if we
81. 3:15apply Union all operator between these
82. 3:18two group by queries we get the result
83. 3:20set that we see on the slide here now in
84. 3:24addition to these two result sets we
85. 3:26also want to compute total salary is
86. 3:30grouped just by gender column since here
87. 3:33we're grouping just by gender country
88. 3:36column is going to be null and to
89. 3:38achieve this as you might have already
90. 3:39guessed we're going to use another
91. 3:42groupby query and that groupby query is
92. 3:44going to be very similar to the group by
93. 3:47query that we have used to produce the
94. 3:49country groupings so I'm going to make a
95. 3:52copy of this second group by
96. 3:56query so now country is going to be null
97. 3:59because we're going to group just by
98. 4:01gender and we want the gender column and
99. 4:05within our group by we're going to use
100. 4:07just the gender column so when we
101. 4:09execute this we should get the total
102. 4:11salaries grouped by gender and now
103. 4:14between all these three Group by queries
104. 4:17I'm going to apply Union all operator so
105. 4:19when we execute this we should get the
106. 4:22result set that we see on the
107. 4:24slide now in addition to these three
108. 4:27result sets we also want the grand total
109. 4:31so here we are not grouping by any
110. 4:33specific column we're not grouping by
111. 4:35gender or country we want the grand
112. 4:38total of salaries you know basically the
113. 4:40total amount of um salary that we paying
114. 4:42for all employees across the globe to
115. 4:46achieve
116. 4:47that I'm going to use another select
117. 4:50query now we don't require any Group by
118. 4:52Clause at all you know country is going
119. 4:55to be null gender is going to be null
120. 4:58and we just want some of salary column
121. 5:00across the entire table so when we
122. 5:03execute this we should get the total
123. 5:05salary that we are paying for all our
124. 5:08employees so now we are going to apply
125. 5:11another Union all operator here so when
126. 5:14we execute this we should get the result
127. 5:17that we see on the
128. 5:18slide now if you look at this query we
129. 5:21definitely have two problems here first
130. 5:23the amount of transact SQL code that we
131. 5:25have to write is huge second to produce
132. 5:29this result result this transact SQL
133. 5:31code is going to hit that employees
134. 5:33table four times so it's not very good
135. 5:36from a performance standpoint as well
136. 5:38now if we use the grouping sets feature
137. 5:41introduced in SQL Server 2008 then the
138. 5:44amount of transact SQL code that we have
139. 5:46to write will be greatly reduced this is
140. 5:48just the amount of code that we have to
141. 5:50write to produce this same result set
142. 5:54okay so let's look at that grouping sets
143. 5:58query So within our results set we want
144. 6:00those three columns country gender and
145. 6:01total salary so the select Clause is not
146. 6:04going to change country gender sum of
147. 6:06salary we giving it an alas total salary
148. 6:09from employees table we're still going
149. 6:11to use group buy because we use grouping
150. 6:13sets with group buy so group buy and
151. 6:15grouping sets so we specify our grouping
152. 6:18sets here so we have an open and a close
153. 6:20parenthesis and then we specify our
154. 6:23groups so we want to first group by
155. 6:26country and genda so the first six rows
156. 6:29here
157. 6:30will be produced by this first grouping
158. 6:34here okay so this six rows you know have
159. 6:39the salary grouped by country and gender
160. 6:42and this is the group that will produce
161. 6:44that and then we are grouping by country
162. 6:46so the next three rows that we have here
163. 6:49so we have got three countries India UK
164. 6:51USA so salary is grouped by Country and
165. 6:54this is the group within our grouping
166. 6:56sets that's going to produce those three
167. 6:58rows and then the rows 10 and 11 are
168. 7:04produced by the third group that we have
169. 7:07here gender and then to produce this
170. 7:09grand total right here we're using this
171. 7:12empty parenthesis so this is basically
172. 7:14telling to SQL Server we want the grand
173. 7:16total of sum of uh you know salary
174. 7:19column all right so let's look at this
175. 7:22in
176. 7:23action let's fire up another query
177. 7:25editor window and I'm going to make a
178. 7:27copy of this select query
179. 7:33so we want to use grouping
180. 7:38sets open parenthesis close parenthesis
181. 7:42and then we specify our groups first we
182. 7:45want to group by country and gender so
183. 7:49this is going to give us the first six
184. 7:51rows and then we want to group just by
185. 7:54country and then just by gender and then
186. 7:59finally lay grand total so when we
187. 8:03execute this
188. 8:04query we should get the result set that
189. 8:08this Union all operator has produced now
190. 8:11if you look at the data first of all
191. 8:13look at the number of rows the union all
192. 8:15query has produced 12 rows the grouping
193. 8:19sets query also produced 12 rows but if
194. 8:21you look at it's the same data but if
195. 8:24you look at the order of the rows here
196. 8:27between the grouping set query and the
197. 8:29union all query it's not the same now if
198. 8:31you want to control the ordering of rows
199. 8:33you can use Auto by Clause with this
200. 8:36grouping sets
201. 8:38query so
202. 8:40for this query you know basically to
203. 8:43produce the data in the same order as
204. 8:46this Union all operator query I'm going
205. 8:48to use order by and we are first going
206. 8:51to order by country grouping and then by
207. 8:53gender grouping and within each gender
208. 8:55grouping we want to order the genders
209. 8:58Okay so
210. 9:05Auto bu I'm going to use the grouping
211. 9:08function and we want to group first by
212. 9:11country grouping and
213. 9:14then by gender
214. 9:17grouping and then finally by gender so
215. 9:20when we execute this now The Ordering of
216. 9:23the data should be same as well between
217. 9:25these two queries
218. 9:32thank you for listening and have a great
219. 9:34day