Calculate running total in SQL Server 2012 — Transcript

Full transcript

1. 0:00this is part 112 of SQL Server tutorial
2. 0:03in this video we'll discuss how easy it
3. 0:05is to calculate running total in SQL
4. 0:07Server 2012 and later versions we'll use
5. 0:11this employees table on the left for the
6. 0:13examples in this video based on this
7. 0:15table we want to write a query which is
8. 0:17going to retrieve name gender salary
9. 0:19columns in addition to these three
10. 0:21columns we also want this running total
11. 0:24column which is going to keep track of
12. 0:26running total of the salary column
13. 0:28notice this running total column starts
14. 0:30up at the first salary which is 5,000
15. 0:33when we add 4,500 to that it is 9,500
16. 0:36when we add 5,500 to 9,500 it is 15,000
17. 0:40and it goes on like that now let's see
18. 0:43how easy it is to compute this running
19. 0:45total column using SQL Server sum
20. 0:47aggregate function along with the Over
21. 0:49clause let's flip to SQL Server
22. 0:51management studio so from this employees
23. 0:53table we want
24. 0:56name
25. 0:58gender salary and then the running total
26. 1:02to compute the running total let's use
27. 1:04the sum aggregate function we're going
28. 1:06to use this function on salary column
29. 1:10and then let's use the Over clause
30. 1:12followed by that Auto
31. 1:14buy and then let's sort the data by the
32. 1:18ID column and then I'm going to call
33. 1:21this
34. 1:23running
35. 1:24total now the first question that comes
36. 1:27to our mind is why am I not using salary
37. 1:30column in the autoby Clause there's a
38. 1:32specific reason for that which we'll
39. 1:34discuss in just a bit for now let's
40. 1:37execute this query and see what we get
41. 1:39notice we get the running total column
42. 1:41and look at the values in this column it
43. 1:42starts off with the first salary which
44. 1:44is 5,000 when we add 4,500 to that it is
45. 1:479,500 when we add 5,500 to that it is
46. 1:5015,000 and it goes on like that at the
47. 1:54moment we are not using Partition by
48. 1:56Clause so the data in this table is not
49. 1:58partitioned in any way we have this
50. 2:00gender column so let's partition the
51. 2:02data by gender column to do that I'm
52. 2:04going to use Partition
53. 2:07by gender let's execute this query and
54. 2:11see what we get the first thing to
55. 2:12notice here is that we get two
56. 2:14partitions the first one is female
57. 2:15employees partition and the next one is
58. 2:17male employees partition and look at
59. 2:19what happens to the running total column
60. 2:22Within These two partitions So within
61. 2:24the female employees partition the first
62. 2:25salary is 5,500 so it starts off with
63. 2:28that when we add 4,000 to that it is
64. 2:319,500 when we add 5,000 to that it is
65. 2:3414,500 and when we add 7,000 to 14,500
66. 2:38it is
67. 2:4021,500 and look at what happens when the
68. 2:43partition changes from female to male it
69. 2:47calculates running total for the male
70. 2:49employees partition separately so the
71. 2:51first salary within male employees
72. 2:53partition is 5,000 so it starts off with
73. 2:56that when we add 4,500 to that it is
74. 2:589,500 when we we add 3,500 to that it is
75. 3:0213,000 so it's that easy to compute
76. 3:06running total whether you have
77. 3:08partitions or whether for the entire
78. 3:11table now let's answer this question why
79. 3:14are we not using salary column in the
80. 3:17order by Clause to answer that first
81. 3:19let's look at the raw data that we have
82. 3:21in our employees table so select sta
83. 3:24from
84. 3:27employees and look at this ID column
85. 3:29this is the primary key column for this
86. 3:31table so the data in this column is
87. 3:34guaranteed to be unique now let's sort
88. 3:37the data
89. 3:38by salary
90. 3:43column now if you notice these three
91. 3:47rowes right here notice that all these
92. 3:49three employees have got the same salary
93. 3:51so there are duplicate values and if you
94. 3:53look at these two employees you know
95. 3:55they have 5,500 as you know the salary
96. 3:59for both of those employees now let's
97. 4:01see what's going to happen when we use
98. 4:03salary column in the order by Clause so
99. 4:06we know that the salary column has got
100. 4:08duplicate values and if we use that
101. 4:11column which has got duplicate values
102. 4:13with the autoby Clause let's see what's
103. 4:15going to happen to keep things simple
104. 4:17I'm going to get rid of this Partition
105. 4:19by Clause from here so let's execute
106. 4:22this query and see what we get so look
107. 4:26at the running total column it starts
108. 4:28off with the first salary which is 3
109. 4:29,500 when we add 4,000 to that it is
110. 4:327,500 when we add 4,500 to that it is
111. 4:3512,000 and when we add 5,000 to 12,000
112. 4:38it should actually be 177,000 but look
113. 4:41at what we getting
114. 4:4327,000 how is that possible that's
115. 4:46possible because what it's doing here is
116. 4:49it's adding these three 5000s that is
117. 4:525,000 + 5,000 + 5,000 which is 15,000 to
118. 4:5712,000 and that is what is giving us
119. 5:00this 27,000 and look at that it gives
120. 5:02the 277,000 for these three rows where
121. 5:05we have those
122. 5:07duplicates and then look at that next we
123. 5:10have two more rows with same salary
124. 5:12which is
125. 5:135,500 so 5,500 + 5,500 that is 11,000
126. 5:18when we add 11,000 to 27,000 it is
127. 5:2138,000 and that's what we get for these
128. 5:25two rows so this may not be what you
129. 5:28want you know according to your business
130. 5:29needs if that's the case you know it's
131. 5:31always better to use a column that has
132. 5:35got unique values in the auto by Clause
133. 5:38that's the reason why I have used ID
134. 5:40column along with Auto buy so when we
135. 5:43use the ID
136. 5:47column I get the running total that I
137. 5:50expect so whenever I add you know 4,500
138. 5:53to 5,000 I get 9,500 when we add 5,500
139. 5:56to 9,500 we get 15,000 and it goes on
140. 5:59like like that so it maintains a true
141. 6:02running
142. 6:04total so here we have the partitions
143. 6:07example and here we have the reasons of
144. 6:09why we're not using you know the salary
145. 6:12column that has got duplicate values
146. 6:14along with the auto by Clause when
147. 6:16Computing running
148. 6:18total thank you for listening and have a
149. 6:20great day