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
€67.35
List Price:  €96.21
You save:  €28.86
£55.83
List Price:  £79.77
You save:  £23.93
CA$100.76
List Price:  CA$143.95
You save:  CA$43.19
A$112.23
List Price:  A$160.33
You save:  A$48.10
S$95.14
List Price:  S$135.93
You save:  S$40.78
HK$543.62
List Price:  HK$776.63
You save:  HK$233.01
CHF 63.04
List Price:  CHF 90.06
You save:  CHF 27.02
NOK kr797.48
List Price:  NOK kr1,139.30
You save:  NOK kr341.82
DKK kr502.49
List Price:  DKK kr717.87
You save:  DKK kr215.38
NZ$124.10
List Price:  NZ$177.29
You save:  NZ$53.19
د.إ257.07
List Price:  د.إ367.26
You save:  د.إ110.18
৳8,361.55
List Price:  ৳11,945.58
You save:  ৳3,584.03
₹5,962.79
List Price:  ₹8,518.63
You save:  ₹2,555.84
RM314.04
List Price:  RM448.65
You save:  RM134.61
₦108,335.42
List Price:  ₦154,771.52
You save:  ₦46,436.10
₨19,495.71
List Price:  ₨27,852.21
You save:  ₨8,356.49
฿2,390.81
List Price:  ฿3,415.60
You save:  ฿1,024.78
₺2,467.44
List Price:  ₺3,525.07
You save:  ₺1,057.62
B$434.14
List Price:  B$620.23
You save:  B$186.09
R1,296.68
List Price:  R1,852.48
You save:  R555.80
Лв131.68
List Price:  Лв188.12
You save:  Лв56.44
₩102,198.93
List Price:  ₩146,004.73
You save:  ₩43,805.79
₪256.56
List Price:  ₪366.53
You save:  ₪109.97
₱4,090.81
List Price:  ₱5,844.26
You save:  ₱1,753.45
¥10,994.99
List Price:  ¥15,707.80
You save:  ¥4,712.81
MX$1,408.98
List Price:  MX$2,012.91
You save:  MX$603.93
QR255.09
List Price:  QR364.43
You save:  QR109.34
P967.82
List Price:  P1,382.66
You save:  P414.84
KSh9,046.20
List Price:  KSh12,923.70
You save:  KSh3,877.50
E£3,562.49
List Price:  E£5,089.49
You save:  E£1,527
ብር8,907.19
List Price:  ብር12,725.10
You save:  ብር3,817.91
Kz63,830.88
List Price:  Kz91,190.88
You save:  Kz27,360
CLP$69,323.69
List Price:  CLP$99,038.09
You save:  CLP$29,714.40
CN¥510.80
List Price:  CN¥729.75
You save:  CN¥218.94
RD$4,245.36
List Price:  RD$6,065.06
You save:  RD$1,819.70
DA9,465.07
List Price:  DA13,522.10
You save:  DA4,057.03
FJ$162.39
List Price:  FJ$232
You save:  FJ$69.60
Q539.11
List Price:  Q770.19
You save:  Q231.08
GY$14,638.77
List Price:  GY$20,913.42
You save:  GY$6,274.65
ISK kr9,772.70
List Price:  ISK kr13,961.60
You save:  ISK kr4,188.90
DH703.98
List Price:  DH1,005.73
You save:  DH301.75
L1,291.33
List Price:  L1,844.84
You save:  L553.50
ден4,143.97
List Price:  ден5,920.21
You save:  ден1,776.24
MOP$559.97
List Price:  MOP$800
You save:  MOP$240.02
N$1,289.45
List Price:  N$1,842.15
You save:  N$552.70
C$2,574.80
List Price:  C$3,678.44
You save:  C$1,103.64
रु9,534.71
List Price:  रु13,621.60
You save:  रु4,086.89
S/260.99
List Price:  S/372.86
You save:  S/111.86
K283.85
List Price:  K405.52
You save:  K121.66
SAR262.91
List Price:  SAR375.60
You save:  SAR112.69
ZK1,936.45
List Price:  ZK2,766.47
You save:  ZK830.02
L335.12
List Price:  L478.77
You save:  L143.64
Kč1,694.03
List Price:  Kč2,420.15
You save:  Kč726.12
Ft27,771.44
List Price:  Ft39,675.18
You save:  Ft11,903.74
SEK kr774.38
List Price:  SEK kr1,106.30
You save:  SEK kr331.92
ARS$71,792.58
List Price:  ARS$102,565.23
You save:  ARS$30,772.64
Bs484.55
List Price:  Bs692.24
You save:  Bs207.69
COP$307,165.41
List Price:  COP$438,826.54
You save:  COP$131,661.12
₡35,530.54
List Price:  ₡50,760.09
You save:  ₡15,229.55
L1,777.33
List Price:  L2,539.15
You save:  L761.82
₲546,250
List Price:  ₲780,390.60
You save:  ₲234,140.59
$U3,115.91
List Price:  $U4,451.49
You save:  $U1,335.58
zł287.82
List Price:  zł411.19
You save:  zł123.37
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.