SQL MAX and MIN Aggregate Functions

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, in this video, we will be looking at our max and min aggregate functions. For this exercise, we start off with the activity to bring back the highest and lowest grade, or grades for each course. And so, as usual, we start off with our basic select star. And then we can assess and see what it is that we're interested in. And that would be the course and the grid. So process of elimination, we just say course, ID, and very so now we're bringing back each course and the grades that have been awarded for these courses.

And I can include a where clause to say where greed is not know. So that I can eliminate those rules that have low grade. So essentially, this sighs should see us bringing back one rule per course. And in this one rule, we should have the highest grade and the lowest grade for any particular course. So let's take for instance course, with ID one, the highest grade awarded so far is 100. And the lowest one is 35.

And so we want one frontals. That's is the course, highest period and lowest grade. As I'm sure we have grasped by know, anytime that we're going to be doing an aggregate or some mathematical function like this, we need or GROUP BY clause. So we see course ID. And because we're going to be doing an aggregate function on greed, then we don't need to include greed here in the group by so if I say Max, then that is the aggregate function that brings back the most or the highest value. One start grouping has occurred.

So if I run this, then we will see that course ID one is bringing about 100 course it to 9095 and 59. But of course, that's not all we've satisfied the requirement to bring about the highest now we need to bring about the lowest and to bring back the lowest is simply using the function mean. So mean grade. And so when it groups it by force, ID is going to say, Okay, what are all the grades Give me back the highest value and giving back the lowest value. Also, you may realize a trend that most of these aggregate functions only work with numbers because, you know, you can find the max name and the min name. But then of course, like count actually is not biased towards any data type.

Because Cohen's is literally just keeping track and presenting a number of the number of occurrences. Alright, so when we have marks In these, remember to only use number values there. And so when we run this query, we see that we have 135 9064. And so this is a nice clean way to bring back a report on each course and this highest scoring or the highest score and the lowest score. So you can go ahead and play around with min and max and as always see how 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.