SQL COUNT Aggregate Function

Microsoft SQL Server 2017 for Everyone Group By and Aggregate Functions
3 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.33
List Price:  €91.91
You save:  €27.57
£54.76
List Price:  £78.24
You save:  £23.47
CA$95.38
List Price:  CA$136.26
You save:  CA$40.88
A$105.05
List Price:  A$150.08
You save:  A$45.03
S$94.33
List Price:  S$134.76
You save:  S$40.43
HK$546.67
List Price:  HK$781
You save:  HK$234.32
CHF 63.74
List Price:  CHF 91.06
You save:  CHF 27.32
NOK kr733.71
List Price:  NOK kr1,048.21
You save:  NOK kr314.49
DKK kr480.07
List Price:  DKK kr685.84
You save:  DKK kr205.77
NZ$113.63
List Price:  NZ$162.33
You save:  NZ$48.70
د.إ257.07
List Price:  د.إ367.26
You save:  د.إ110.18
৳8,211.53
List Price:  ৳11,731.26
You save:  ৳3,519.73
₹5,822.15
List Price:  ₹8,317.71
You save:  ₹2,495.56
RM328.51
List Price:  RM469.33
You save:  RM140.81
₦103,757.37
List Price:  ₦148,231.17
You save:  ₦44,473.80
₨19,474.72
List Price:  ₨27,822.22
You save:  ₨8,347.50
฿2,564.11
List Price:  ฿3,663.18
You save:  ฿1,099.06
₺2,252.35
List Price:  ₺3,217.78
You save:  ₺965.43
B$362.10
List Price:  B$517.30
You save:  B$155.20
R1,286.74
List Price:  R1,838.27
You save:  R551.53
Лв125.83
List Price:  Лв179.77
You save:  Лв53.93
₩95,104.15
List Price:  ₩135,868.90
You save:  ₩40,764.74
₪257.31
List Price:  ₪367.61
You save:  ₪110.29
₱4,058.68
List Price:  ₱5,798.37
You save:  ₱1,739.68
¥10,979.06
List Price:  ¥15,685.04
You save:  ¥4,705.98
MX$1,167.55
List Price:  MX$1,668
You save:  MX$500.45
QR255.10
List Price:  QR364.44
You save:  QR109.34
P950.14
List Price:  P1,357.41
You save:  P407.26
KSh9,273.67
List Price:  KSh13,248.67
You save:  KSh3,975
E£3,325.22
List Price:  E£4,750.52
You save:  E£1,425.30
ብር3,990.85
List Price:  ብር5,701.46
You save:  ብር1,710.61
Kz59,620.28
List Price:  Kz85,175.48
You save:  Kz25,555.20
CLP$63,105.46
List Price:  CLP$90,154.52
You save:  CLP$27,049.06
CN¥497.57
List Price:  CN¥710.85
You save:  CN¥213.27
RD$4,120.99
List Price:  RD$5,887.39
You save:  RD$1,766.39
DA9,415.45
List Price:  DA13,451.22
You save:  DA4,035.77
FJ$155.95
List Price:  FJ$222.79
You save:  FJ$66.84
Q543.57
List Price:  Q776.56
You save:  Q232.99
GY$14,648.31
List Price:  GY$20,927.06
You save:  GY$6,278.74
ISK kr9,619.42
List Price:  ISK kr13,742.62
You save:  ISK kr4,123.20
DH694.87
List Price:  DH992.71
You save:  DH297.84
L1,239.53
List Price:  L1,770.84
You save:  L531.30
ден3,964.07
List Price:  ден5,663.20
You save:  ден1,699.13
MOP$562.84
List Price:  MOP$804.09
You save:  MOP$241.25
N$1,285.31
List Price:  N$1,836.24
You save:  N$550.92
C$2,575.71
List Price:  C$3,679.74
You save:  C$1,104.03
रु9,302.62
List Price:  रु13,290.03
You save:  रु3,987.40
S/261.94
List Price:  S/374.22
You save:  S/112.27
K272.11
List Price:  K388.75
You save:  K116.63
SAR262.50
List Price:  SAR375.02
You save:  SAR112.51
ZK1,873.65
List Price:  ZK2,676.76
You save:  ZK803.10
L320.13
List Price:  L457.35
You save:  L137.22
Kč1,586.33
List Price:  Kč2,266.28
You save:  Kč679.95
Ft24,682.62
List Price:  Ft35,262.40
You save:  Ft10,579.78
SEK kr737.43
List Price:  SEK kr1,053.51
You save:  SEK kr316.08
ARS$62,536.06
List Price:  ARS$89,341.06
You save:  ARS$26,805
Bs483.53
List Price:  Bs690.78
You save:  Bs207.25
COP$270,803.02
List Price:  COP$386,878.04
You save:  COP$116,075.02
₡35,985.46
List Price:  ₡51,410
You save:  ₡15,424.54
L1,729.24
List Price:  L2,470.46
You save:  L741.21
₲526,430.33
List Price:  ₲752,075.57
You save:  ₲225,645.23
$U2,694.45
List Price:  $U3,849.38
You save:  $U1,154.93
zł273.60
List Price:  zł390.87
You save:  zł117.27
Already have an account? Log In

Transcript

Hey guys. So we're continuing our exploration of aggregate functions in SQL, and today we'll be looking at columns. Now our scenario is to bring back the number of students enrolled per course. So we can just start up our query, usually where we select star from our enrollments table. And we look at that data set. And what we want.

What we're interested in is the number of students per course. So we know we need the course to be filtered out based on its ID. And then we need to count the number of students that match each course. So that means course one should have 123 students, and the same logic would apply for every other course. So firstly, process of elimination, what I really need for this scenario, I need a course ID and I need this Student ID. And when I do that, we only bring back those two values.

And by now, we should all be experts on joins and inner join, some would not want to focus on inner joining and making this beautiful, I just want us to focus on the function that is the aggregate functions. So the next thing is that we want to use an aggregate function to columns, the number of students, which means that for chorus, one, I should only see one and three. And even though you see that here, when we finish the query, I shouldn't be seeing one and three, one and six, one and 12. But I should just be seeing one. One rule with course ID one on a count of three. And the same would apply for every other course ID that is present with the number of students present.

So I can just use my aggregate function. And then I will get an arrow and I get this arrow because it's it's telling us that we have to use group by so that's just a warning, you can't use an aggregate function unless you're grouping by. So the next step would be to group by. And remember that we're not grouping by whatever column is being aggregated. So we use group by course ID only. And then when I execute this, I get back course ID one with a count of three, course ID two with a count of four.

So that means that course for each course I'm seeing how many students are enrolled to this course. Alright, so just took a few minutes and modify this course so it looks a bit more beautiful on the printout. So I just included the course code. Well, I included the courses table through an inner join here. And then I included the columns the course code, give it a nice beautiful heading the title and gave our cones portion a nice fitting also. And of course, because the cardinal rule is once your grouping by you have to include their distinct columns, I had to group by the title and the course code.

So because those are repeats the group by clump them together, but also included an count for the number of chomped records. And so that's how we're able to use this aggregate function to get a count. And so down here, you'll see that this is the output. And this looks like something you could actually turn into your administrator when they send a request for such a report. So you can go ahead and play with this the script file is included for further reference and have fun

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.