HAVING explained
Meet Mary, an avid runner and fitness guru.
Mary is competing to be selected to participate in The Best Race Ever against 2 other runners, Sam and David.
Each runner must run a mile 3 times and record their times.
Mary ran the following times: 8:05, 7:45, 8:07.
Sam ran the following times: 8:17, 8:01, 7:54.
David ran the following times: 7:48, 8:00, 8:06.
In order to make the cut to run in The Best Race Ever, the runner's average mile time must be below 8 minutes.
So, let's find each runners' average time. First, we'll convert their times to seconds.
Mary: 485, 465, 487
Sam: 497, 481, 474
David: 468, 480, 492
Next, we'll find the average seconds…
Mary: (485 + 465 + 487) / 3 = 479 avg. seconds
Sam: (497 + 481 + 474) / 3 = 484 avg. seconds
David: (468 + 480 + 486) / 3 = 478 avg. seconds
…and divide by 60 seconds to find the average minutes.
Mary: 479 / 60 = 7.98 avg. minutes
Sam: 484 / 60 = 8.07 avg. minutes
David: 478 / 60 = 7.97 avg. minutes
Now, let's cross out any times that aren't below 8 minutes.
Mary: 479 / 60 = 7.98 avg. minutesSam: 484 / 60 = 8.07 avg. minutes
David: 478 / 60 = 7.97 avg. minutes
Looks like Mary and David made the cut for The Best Race Ever!
In this situation, we essentially performed the purpose of a HAVING statement. We took groups of running times for each runner, averaged them, and then filtered out any results that weren't below 8 minutes.
HAVING statements are used to filter the results of a query, based on aggregate function outputs.
How to code a HAVING
Here's the template for a HAVING statement:
PAID CONTENT This is the end of the preview. To unlock the rest, get the SQL Cram Kit, Lifetime Access or BUS 104 Cram Kit Bundle.
Already purchased? Click here to log in.