Grouping function in SQL Server — Transcript

Full transcript

1. 0:00this is part 105 of SQL Server tutorial
2. 0:03in this video we'll discuss grouping
3. 0:05function in SQL Server this is
4. 0:07continuation to part 104 so please watch
5. 0:09part 104 before proceeding what is
6. 0:12grouping function grouping function
7. 0:14indicates whether the column in a group
8. 0:15by list is aggregated or not grouping
9. 0:19returns one for aggregated or zero for
10. 0:21not aggregated in the result set let's
11. 0:24understand what we mean by the statement
12. 0:25with an example here we have total sales
13. 0:29aggregated across these three columns
14. 0:31continent country and city and then we
15. 0:34have three other columns here so
16. 0:35basically these three columns are going
17. 0:37to tell us whether if this total sales
18. 0:40value is aggregated across any of these
19. 0:44columns so if you look at the first row
20. 0:46this total sales figure is not
21. 0:49aggregated by any specific column so
22. 0:52notice gpor continent gpor country and
23. 0:54gpor city all of them are zero
24. 0:57indicating that data is not aggregated
25. 1:00for any of those columns whereas if you
26. 1:02look at this third row look at that city
27. 1:05is null so this subtotal here 3,000 this
28. 1:09total sales is for all cities within
29. 1:12India country that is present in Asia
30. 1:14continent so this figure is aggregated
31. 1:18across City column so notice gpor city
32. 1:22is one whereas the other two columns are
33. 1:25zero now if you look at this row right
34. 1:28here this subtotal is for all cities and
35. 1:31for all countries in Asia continent and
36. 1:34if you look at the respect to gpor
37. 1:36columns here notice country and city are
38. 1:39one whereas continent is zero and if you
39. 1:42look at the last row here all of the
40. 1:44column values are null so this is the
41. 1:47grand total so in this case the data is
42. 1:51aggregated across all those three
43. 1:52columns notice GP uncore column values
44. 1:56here for all the columns are ones so
45. 1:59let's see how to produce this output
46. 2:01using this grouping function so this
47. 2:04function is going to return one if the
48. 2:06data is aggregated across the column
49. 2:09that we specify otherwise it's going to
50. 2:11return zero so let's flip to SQL
51. 2:14management studio so this is the same
52. 2:16query that we worked with in the
53. 2:17previous video session so when we
54. 2:19execute this we get the total sales
55. 2:21grouped by continent country and city
56. 2:25now we also want these three columns
57. 2:28gpor continent GP country and gpor city
58. 2:32for that we can use the grouping
59. 2:34function now one important thing to keep
60. 2:36in mind is that with this grouping
61. 2:37function you can only specify one column
62. 2:40that means here to get these three
63. 2:42columns you will have to use grouping
64. 2:44function three times so let's look at
65. 2:46that in action
66. 2:48now so along with the sale amount we
67. 2:52want to use the grouping function and
68. 2:55I'm going to
69. 2:56pass
70. 2:58continent and let's give it written alas
71. 3:03gpor
72. 3:05continent and similarly we want the same
73. 3:07thing for country and
74. 3:10city so grouping of country as gpor
75. 3:17country
76. 3:18and grouping of city as
77. 3:24gpor City so let's execute this and
78. 3:29notice that if data is aggregated across
79. 3:31a column we get one for that gpor column
80. 3:36if it's not aggregated we get zero all
81. 3:40right now the next obvious question that
82. 3:42comes to our mind is okay I know what is
83. 3:44grouping function but what is the use of
84. 3:47this grouping function in the real world
85. 3:50now look at this when we have you know
86. 3:54this total sales amount aggregated by
87. 3:57default in the result set it will
88. 3:58display null value value but if you look
89. 4:00at from the end user perspective null
90. 4:03value doesn't make any sense at all
91. 4:05instead of nulls don't you think it
92. 4:07makes sense to display a word like this
93. 4:09all or all cities which is more
94. 4:12meaningful So when you say all here you
95. 4:14know the user can read this as okay this
96. 4:17total sales amount is for all cities in
97. 4:21India country that's present in Asia
98. 4:23continent whereas if you look at all
99. 4:25here then this clearly says to the end
100. 4:28user this is the grand total of total
101. 4:29sales across all cities all countries
102. 4:32and all continents okay so we can use
103. 4:35this grouping function to achieve this
104. 4:38now if you look at the result here
105. 4:39notice that you know by whatever column
106. 4:41we have the data aggregated across those
107. 4:43columns we have null values now we want
108. 4:47to replace those null values with this
109. 4:50all word so let's see how to do
110. 4:53that so first of all in our result set
111. 4:56we don't need these three additional
112. 4:58columns so I'm going to remove that
113. 5:00and we want to use the grouping function
114. 5:03so in order to replace the null value
115. 5:06with this word all I'm going to use the
116. 5:08case statement and grouping
117. 5:11function so
118. 5:14select
119. 5:16case
120. 5:20when grouping
121. 5:22of let's take this continent pass it to
122. 5:26our grouping function so when grouping
123. 5:28of continent equals 1 that means data is
124. 5:32aggregated across that column so if
125. 5:35that's the case we know that we are
126. 5:36going to get a null value and at that
127. 5:39point we want to display all instead of
128. 5:42null
129. 5:44else so if it's not grouped across that
130. 5:47continent then what do we want to
131. 5:48display we want to display the
132. 5:50respective continent name now if you
133. 5:53look at the data that we have we don't
134. 5:54have any null values within continent
135. 5:57country or city columns but if if we
136. 5:59have null values we don't want to
137. 6:01display null instead we want to display
138. 6:03you know unknown for either continent
139. 6:06country or city so what I'm going to do
140. 6:08here is use another isnull
141. 6:11function and pass the continent column
142. 6:14to it so if the continent column is null
143. 6:18then I want to display you know this
144. 6:21word unknown instead of null in the
145. 6:24result set and then let's end the case
146. 6:27statement as let's give the column and
147. 6:30alas we want that column name to be
148. 6:33continent
149. 6:35okay so we need to do the same thing for
150. 6:38country and city as well so I'm going to
151. 6:40make a copy of that and let's pass
152. 6:46country so grouping of country is equal
153. 6:49to one then all else if country is null
154. 6:53again display unknown and and we want
155. 6:56the column name to be country and let's
156. 6:58do the same thing
157. 7:00for
158. 7:02City so when grouping of city is equal
159. 7:05to one then all else check if City
160. 7:08column has got a null value if that's
161. 7:10the case uh display unknown so by the
162. 7:13way we have unknown spelling wrong here
163. 7:15let's correct
164. 7:19that and let's give this an alas of city
165. 7:24and then we want
166. 7:26the total sales amount all right so
167. 7:30let's go ahead and execute this one now
168. 7:33look at that instead of nulls we have
169. 7:36all so now if you look at this one
170. 7:37notice that this subtotal is for all
171. 7:40cities all countries in Asia continent
172. 7:43whereas this is a grand total it's for
173. 7:45all cities all countries and all
174. 7:47continents so much you know better than
175. 7:51what we had
176. 7:52before all right so here is that case
177. 7:55statement that we have just looked at
178. 7:57now the next obvious question that comes
179. 7:59to our mind is why can't I use e null
180. 8:03function instead of grouping function to
181. 8:05achieve the same result well we can use
182. 8:08it as long as we don't have any null
183. 8:11data within the sales table let me
184. 8:14explain what I mean so first of all
185. 8:16let's see when we use eal function
186. 8:18instead of you know grouping function if
187. 8:20we get the same result I'm actually
188. 8:21going to make a copy of this query so
189. 8:24let's open another query editor
190. 8:27window now let's let's use eal function
191. 8:30so I'm going to get rid of this big case
192. 8:33statement and let's use
193. 8:35eal eal of
194. 8:41continent so if continent is null then
195. 8:45we want to display this word all let's
196. 8:49do the same thing for country and
197. 8:55city okay so if country is null then
198. 9:00display all if city is null then display
199. 9:03all so now when I execute this one
200. 9:06notice that okay obviously we have to
201. 9:07give that column an alas so let's give
202. 9:10it an alas as continent and let's do the
203. 9:13same thing for City and
204. 9:19Country as
205. 9:23country and let's do it for city as
206. 9:28well so now when we execute this we get
207. 9:32the same result right so when we use eal
208. 9:36a grouping function we are able to
209. 9:37achieve that
210. 9:39now we can use e null function to
211. 9:42achieve the same thing as long as our
212. 9:45raw data in the sales table does not
213. 9:47contain any null values let me explain
214. 9:49what I mean so at the moment if you look
215. 9:51at the sales table let's execute a
216. 9:55select query on the sales
217. 9:57table so this the table that we created
218. 10:00in our previous video session so if you
219. 10:02look at this continent country and city
220. 10:04columns we don't have null values
221. 10:06anywhere now let's go ahead and change
222. 10:09you know the City Bangalore to null and
223. 10:11to do that let's issue an update
224. 10:13statement so
225. 10:18update sales
226. 10:20table set City
227. 10:24equals null and the ID of that row is
228. 10:27one so where ID equals 1 so now let me
229. 10:31execute this and now when we select the
230. 10:34data from the Sals table notice that for
231. 10:36the first row for Asia continent India
232. 10:38country you know cities now now when we
233. 10:42execute this query look at what's going
234. 10:44to happen since within the data itself
235. 10:47we have null you know it's replacing
236. 10:50that also with all and this is kind of
237. 10:53misleading so you know if we read this
238. 10:56from an end user perspective it's like
239. 10:58for all cities within India country in
240. 11:01Asia continent the total sales is one
241. 11:04okay now why did we get all there that's
242. 11:06because the raw data had a null value
243. 11:08and because we are using e null function
244. 11:10here wherever continent is null we
245. 11:12replacing that with all and that's the
246. 11:14reason why we got it there whereas when
247. 11:17we use this
248. 11:19query notice that we don't have that
249. 11:21problem so when city is null it's
250. 11:26displaying that as unknown we don't have
251. 11:27a city name there so it's displaying
252. 11:30unknown but not all okay so now this is
253. 11:33much readable that's why grouping is
254. 11:36very
255. 11:39useful now this grouping function can be
256. 11:42used with any of these functions rollup
257. 11:44Cube or grouping sets so at the moment
258. 11:47we using rollup when I use Cube and when
259. 11:51we have the data aggregated across
260. 11:52columns we are going to get you know the
261. 11:55same result and when we use grouping
262. 11:57sets as well so
263. 11:59instead of cube when we
264. 12:02use grouping sets again we can still
265. 12:12use grouping function so this is the
266. 12:16first group by which we want to group
267. 12:18the total sale amount then we want it to
268. 12:21group by continent country and just by
269. 12:25continent finally
270. 12:30grand total so here we using grouping
271. 12:32sets so now when we execute this we
272. 12:36still get
273. 12:43all thank you for listening and have a
274. 12:45great day