SQL SUM Aggregate Function

Microsoft SQL Server 2017 for Everyone Group By and Aggregate Functions
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$69.99
List Price:  $99.99
You save:  $30
€64.25
List Price:  €91.79
You save:  €27.53
£55.26
List Price:  £78.95
You save:  £23.68
CA$95.30
List Price:  CA$136.16
You save:  CA$40.85
A$104.44
List Price:  A$149.21
You save:  A$44.76
S$94.20
List Price:  S$134.58
You save:  S$40.38
HK$546.10
List Price:  HK$780.18
You save:  HK$234.07
CHF 63.82
List Price:  CHF 91.18
You save:  CHF 27.35
NOK kr751.73
List Price:  NOK kr1,073.95
You save:  NOK kr322.21
DKK kr480.45
List Price:  DKK kr686.39
You save:  DKK kr205.93
NZ$114.09
List Price:  NZ$162.99
You save:  NZ$48.90
د.إ257.07
List Price:  د.إ367.26
You save:  د.إ110.19
৳8,153.02
List Price:  ৳11,647.67
You save:  ৳3,494.65
₹5,830.20
List Price:  ₹8,329.22
You save:  ₹2,499.01
RM328.07
List Price:  RM468.70
You save:  RM140.62
₦102,885.30
List Price:  ₦146,985.30
You save:  ₦44,100
₨19,393.48
List Price:  ₨27,706.17
You save:  ₨8,312.68
฿2,519.17
List Price:  ฿3,598.96
You save:  ฿1,079.79
₺2,256.71
List Price:  ₺3,224.01
You save:  ₺967.30
B$357.27
List Price:  B$510.40
You save:  B$153.13
R1,282.86
List Price:  R1,832.74
You save:  R549.87
Лв125.91
List Price:  Лв179.88
You save:  Лв53.96
₩94,793.75
List Price:  ₩135,425.45
You save:  ₩40,631.70
₪258.47
List Price:  ₪369.26
You save:  ₪110.78
₱4,048.92
List Price:  ₱5,784.42
You save:  ₱1,735.49
¥10,895.69
List Price:  ¥15,565.94
You save:  ¥4,670.25
MX$1,162.43
List Price:  MX$1,660.69
You save:  MX$498.25
QR253.91
List Price:  QR362.75
You save:  QR108.83
P943.39
List Price:  P1,347.76
You save:  P404.36
KSh9,085.75
List Price:  KSh12,980.20
You save:  KSh3,894.45
E£3,276.75
List Price:  E£4,681.28
You save:  E£1,404.52
ብር3,999.12
List Price:  ብር5,713.27
You save:  ብር1,714.15
Kz59,421.51
List Price:  Kz84,891.51
You save:  Kz25,470
CLP$62,605.89
List Price:  CLP$89,440.82
You save:  CLP$26,834.93
CN¥505.55
List Price:  CN¥722.24
You save:  CN¥216.69
RD$4,078.44
List Price:  RD$5,826.59
You save:  RD$1,748.15
DA9,423.52
List Price:  DA13,462.75
You save:  DA4,039.23
FJ$155.95
List Price:  FJ$222.79
You save:  FJ$66.84
Q540.94
List Price:  Q772.81
You save:  Q231.86
GY$14,566.49
List Price:  GY$20,810.17
You save:  GY$6,243.67
ISK kr9,646.55
List Price:  ISK kr13,781.37
You save:  ISK kr4,134.82
DH693.20
List Price:  DH990.33
You save:  DH297.13
L1,231.95
List Price:  L1,760
You save:  L528.05
ден3,957.99
List Price:  ден5,654.52
You save:  ден1,696.52
MOP$559.54
List Price:  MOP$799.37
You save:  MOP$239.83
N$1,273.79
List Price:  N$1,819.78
You save:  N$545.99
C$2,563.07
List Price:  C$3,661.69
You save:  C$1,098.61
रु9,282.12
List Price:  रु13,260.75
You save:  रु3,978.62
S/260
List Price:  S/371.45
You save:  S/111.44
K270.37
List Price:  K386.26
You save:  K115.89
SAR262.46
List Price:  SAR374.96
You save:  SAR112.50
ZK1,777.13
List Price:  ZK2,538.86
You save:  ZK761.73
L320.28
List Price:  L457.57
You save:  L137.28
Kč1,590.03
List Price:  Kč2,271.57
You save:  Kč681.54
Ft24,904.88
List Price:  Ft35,579.92
You save:  Ft10,675.04
SEK kr750.08
List Price:  SEK kr1,071.59
You save:  SEK kr321.50
ARS$61,703.42
List Price:  ARS$88,151.52
You save:  ARS$26,448.10
Bs481.07
List Price:  Bs687.27
You save:  Bs206.20
COP$266,463.69
List Price:  COP$380,678.73
You save:  COP$114,215.04
₡35,632.87
List Price:  ₡50,906.29
You save:  ₡15,273.41
L1,723.87
List Price:  L2,462.78
You save:  L738.91
₲521,670.94
List Price:  ₲745,276.15
You save:  ₲223,605.20
$U2,715.34
List Price:  $U3,879.22
You save:  $U1,163.88
zł274.22
List Price:  zł391.77
You save:  zł117.54
Already have an account? Log In

Transcript

Hey guys, in this video we'll be looking at the sum aggregate function. Our scenario today is to bring back the total number of credits being done by each student. And so we'll have to get a bit creative here because in order to get this data we need to see the students information will see the course information because our courses table has the number of credits. And of course, all of that is combined within the enrollments table. So we'll need data from three different touch points in order to form or aggregate output. And so for me, Ground Zero is our enrollments table.

So I select star from enrollments and then I start adding on what I need. So I know I want students details. So I'm just going to energize the students table. Alright, and having done that inner join on the students table, and I'm going to also Have the inner join the courses table. All right now I have a full party on my hands. So we have data coming in from all three tables, enrollment students and courses.

And no, we can start filtering on what is most important for us to have a sense of the output. So we want to see the total number of credits that each student is doing. So, of course, we will know we need the credit counts, so see that number of credits. And we'll also probably want the student name since we already energized so I'll just go ahead and fill in our column for the full name. Alright, so I just amended my query to only bring back the number of credits and the student's full name and of course, we can just add pretty titles to those. Now the next thing I want to do of course, is to group so I will just group by and what we want to buy is any column that We have included in our select.

So I have to group by first name, after group by last name. And I'll just copy and paste and I'm in a split in the comma separation. And I have to group by the number of credits. Now you see that this, this has reduced because maybe our team is taking more than one three credit courses. And so it's shown to them together in proper and true group by fashion. But the fact is that all work is not complete, because what we want to do is find a total number of credits per student.

And so we'll have to employ our aggregate function called sum, which actually says that for all of those that you're grouping, give me the total so I can just grab the number of credits, inside sum. And if you were kicking and screaming when I included the number of credits down here in the group by then you were right. So we We're about to do the aggregates on number of credits, I don't need to include number of credits in the group by actually I shouldn't include it in the Roubaix. And so this query will actually give me how many credits Judy's doing, how many credits petroi is doing, and etc, etc. And all of this is based on the enrollments table because the enrollments table is keeping track of which classes each one is doing. So the group bys are seeing that each student and grouping them and I am summing up the additional data, which in this case is the number of credits and then bringing back that total.

And that is essentially how the sum aggregate function works. I hope you have fun with it and experiment with it. Bring up another scenario, the script file is already included with this video. And based on the data that you have or how you design your database, feel free to experiment and use it

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.