Over clause in SQL Server — Transcript

Full transcript

1. 0:00this is part 108 of SQL Server tutorial
2. 0:03in this video we'll discuss the power
3. 0:05and use of Over clause in SQL Server the
4. 0:08Over clause combined with Partition by
5. 0:11is used to break up table data into
6. 0:13partitions we have the syntax here first
7. 0:15we specify a SQL Server function we can
8. 0:17use any of these functions sum min max
9. 0:20Etc and then we use the over keyword
10. 0:23followed by that Partition by and then
11. 0:26by whatever columns we want to partition
12. 0:28that table data we have an example here
13. 0:30let's say we have employees table and
14. 0:32within that table we have gender column
15. 0:34so here we using the count aggregate
16. 0:37function over Partition by gender so
17. 0:40this is basically going to tell
18. 0:42partition the employees table data by
19. 0:45gender column so it's going to divide
20. 0:47that employees data into two partitions
21. 0:50male and female and then this count
22. 0:52aggregate function is applied over each
23. 0:54of those partitions meaning we will get
24. 0:56count of female and male employees let's
25. 0:59look at this action so here we have our
26. 1:02employees table here is the gender
27. 1:04column now what we want to do is based
28. 1:06on this table we want to write a query
29. 1:08which is going to give us aggregated
30. 1:10data by gender column so we want the
31. 1:13count of female and male employees
32. 1:15similarly we want to find out what is
33. 1:17the average salary for female employees
34. 1:19and male employees and similarly their
35. 1:21men and Max salaries so at the moment
36. 1:23notice we have got four female and six
37. 1:25male employees now we can very easily
38. 1:28achieve this using a simple group Group
39. 1:30by query we want the data to be
40. 1:32aggregated by gender column so we are
41. 1:35using Group by on the gender column and
42. 1:39to get the totals for genders we are
43. 1:42using the count function to get the
44. 1:43average we are using average to get
45. 1:46minimum of salary we are using Min and
46. 1:48to get the max we are using Max okay a
47. 1:50simple Group by query I have this exact
48. 1:53same query already typed here so when we
49. 1:55execute this we get the result that we
50. 1:57expect so straightforward so the date
51. 2:00that we see in the result set is
52. 2:02aggregated data now what if we want
53. 2:05along with that aggregated data we also
54. 2:08want non-aggregated values like name and
55. 2:11salary so if you look at this aggregated
56. 2:14data notice that we have four female
57. 2:16employees and six male employees now in
58. 2:19the result set right here we have the
59. 2:22names and salaries of individual
60. 2:24employees that's you know name and
61. 2:26salary is not aggregated okay so what we
62. 2:29want is this non-aggregated values along
63. 2:32with these aggregated data for all
64. 2:34female employees look at that the grand
65. 2:36total I mean the gender total is four
66. 2:38for all male employees it is six and
67. 2:41average um salary for female employees
68. 2:44is 5375 so it's the same for all female
69. 2:47employees similarly 5,000 is the average
70. 2:49salary for male employees so the
71. 2:53aggregated data is repeated for each
72. 2:55employee so we want aggregated and
73. 2:58non-aggregated data now the first thing
74. 3:00that comes to our mind is well why don't
75. 3:02we use name and salary column in the
76. 3:04select list if we do that since this a
77. 3:07group by query and name and salary are
78. 3:10not in group by clause and we are not
79. 3:13using any of the aggregate functions on
80. 3:15those two columns so we will get this
81. 3:17error so it's saying employees. name so
82. 3:20name column is coming from employees
83. 3:22table so column employees. name is
84. 3:25invalid in the select list because it's
85. 3:27not contained in either an aggregate
86. 3:30function or in the group by Clause okay
87. 3:34let's actually look at that in action so
88. 3:36if I try and include name comma salary
89. 3:40columns and then if we try to execute
90. 3:42this query we are going to get exactly
91. 3:43the same error that we have seen on the
92. 3:46slide so one way to achieve this result
93. 3:49is by including all these aggregations
94. 3:52in a subquery and then combining that
95. 3:55subquery with the outer query let's
96. 3:57actually look at that in action so what
97. 3:59I'm going to to do is take this subquery
98. 4:02so when we execute this we get um you
99. 4:05know average min max salaries and the
100. 4:08count aggregated by gender so I'm going
101. 4:10to put this in a subquery so let's turn
102. 4:14this into a
103. 4:16subquery and let's call this genders
104. 4:20okay and then I'm going to use an outer
105. 4:22query so from the outer query we want
106. 4:25the name of the employee we want their
107. 4:28salary and we also want the agenda and
108. 4:31we want that from employees table so I'm
109. 4:36going to
110. 4:38join this outer query with the subquery
111. 4:41so let's
112. 4:43use in a
113. 4:45join so we joining employees with gender
114. 4:48so we need to specify the join condition
115. 4:52so on genders do gender so the common
116. 4:57column between employees table and this
117. 4:59genders result set is gender so genders
118. 5:03do genda
119. 5:06equals employees dot
120. 5:10gender all right so what do we want in
121. 5:13the result we want name salary gender we
122. 5:15want gender totals average salary Min
123. 5:17salary and maximum salary these will
124. 5:20come from our subquery because that's
125. 5:22what contains our aggregation so when we
126. 5:25execute this we should get this result
127. 5:28so what I'm going to do here is use this
128. 5:31genders result set and it has got gender
129. 5:34total similarly genders do average
130. 5:40salary
131. 5:42and genders do minimum
132. 5:46salary genders do maximum salary all
133. 5:50right let's go ahead and execute our
134. 5:52query now we get an error and it says
135. 5:55ambiguous column name gender so if you
136. 5:56look at this gender column it's present
137. 5:58both in the employ employees table and
138. 6:00this gender result set so we'll have to
139. 6:03tell from where we want that column we
140. 6:05could either use employees table or this
141. 6:07gender result set it doesn't really
142. 6:09matter for the purpose of this query I'm
143. 6:11going to use employees table all right
144. 6:14so let's execute this query now and look
145. 6:17at the result this is exactly what we
146. 6:19see on the slide all right but look at
147. 6:22the amount of code we have to write to
148. 6:24produce this simple result set now it'll
149. 6:27be much easier if we use Over clause
150. 6:30with Partition bu let's look at that
151. 6:32query
152. 6:35now so what do we want when the result
153. 6:37set we want actually I'm going to make a
154. 6:40copy of this so we
155. 6:42want name
156. 6:46salary
157. 6:49gender from employees
158. 6:52table now along with the gender we want
159. 6:56gender total average salary men and Max
160. 6:58salaries
161. 7:00so to get gender totals I'm going to use
162. 7:03the count function and I'm going to
163. 7:05specify a column name here count of
164. 7:08gender we will use the over keyword over
165. 7:12and then followed by open parenthesis
166. 7:14close
167. 7:15parenthesis Partition by gender column
168. 7:20so what is this going to do this is
169. 7:22going to partition this employees table
170. 7:24data by gender so we will have two
171. 7:26partitions male and female and then this
172. 7:29count function will be applied for each
173. 7:30of those partitions so we are going to
174. 7:33get count of male and female employees
175. 7:36and let's give this column an alas let's
176. 7:38call it genders
177. 7:42total so when we execute
178. 7:45this look at what we get we get name of
179. 7:48the employee salary gender column and
180. 7:50gender totals four female employees and
181. 7:52six male employees now getting average
182. 7:55salary Min salary and maximum salary
183. 7:57it's pretty much identical so I'm
184. 7:59actually going to make a copy of this we
185. 8:02I'm going to now use the average
186. 8:04function but we want the average of
187. 8:06salary column so average of salary over
188. 8:10Partition by gender and I'm going to
189. 8:12call this
190. 8:14AVG salary okay so similarly let's go
191. 8:19ahead and do the same thing for Min and
192. 8:20Max so minimum of salary and let's call
193. 8:24the column Min salary and here it's
194. 8:27going to be Max salary and the function
195. 8:30that we are going to use is
196. 8:32Max all right let's get rid of this last
197. 8:36comma Now when we execute this query
198. 8:39look at that we get exactly the same
199. 8:41result but look at the amount of code
200. 8:43that we have to write this query is also
201. 8:46easier on the eyes it's very easy to
202. 8:49understand so partition the table data
203. 8:51by gender column and then apply these
204. 8:54aggregate
205. 8:56functions which you know will produce
206. 8:59the result that we
207. 9:01want so here is our over with Partition
208. 9:06by
209. 9:08query thank you for listening and have a
210. 9:11great day