>> Join me, Buck Woody and Marisa Matthews as we guide you
through the learning T-SQL modules on Microsoft Learn.
[MUSIC]
>> Working data with T-SQL is pretty
cool but I'm thinking for my reporting,
do I want to group things together or total them up?
>> That brings us not only to working with data but
to applying some functions over the data,
like your groupings or totaling, and so on.
Functions are just built-in statements and
code we want to use to do things.
Let me show you a few types.
I'm just going to use the query editor
here once again as just a display.
Scalar values operate on
a single row and get a single value back,
logical compare two values to determine a single output,
rankings operate on a partition or set of rows,
row sets return our virtual table
that you can use in a from clause,
we'll take a look at that and then the one
you're talking about a lot as aggregates,
we can take a lot of one or more input values
and return a single summarizing value.
>> Great. How can we use to do this?
>> Let's see. We'll start with some scalar functions.
There's actually quite a few functions,
more even than we're showing you here and you
can read about those in the documentation.
One of the most common use cases for
functions are to work with dates and times.
You'll see that in our design we use
the date-time data type for the events table.
The date-time stores not only month, day,
and year of a value,
but also the hours, minutes,
and even seconds of that value.
But perhaps we just want to know all of
the events happening in 2023.
We can use a function to reach in and
get just the year part of the start date value.
Let me show you how that works.
I'll paste this and then we're going to select
the name from the events where,
and here's our function.
You see it starts with the word year in this case,
that's the name of the function,
we open the parenthesis and we put what we want it to operate on.
It's going to go get the start date value and then
just bring back the year and then it's
going to compare that to 2023.
Let's take a look and here are the events that happened in 2023.
By the way, there's also a day and even math we can
do on dates with another function called Datedif,
D-I-F. We've actually already
used logical functions in our select predicate,
remember when we use the like,
and, and or.
We can also use logical functions to
reverse the condition such as adding not.
Instead of getting what we asked for,
we negate those values.
>> That's pretty handy. I will
definitely be using that date function.
I have to admit this is a lot easier and more
profitable than working with the stretchy I hate to admit it.
>> It really is. Another interesting function, by the way,
allows us to show data by a rank or its place in a set.
For instance, assume you want to know where
the times rank in minutes from our sessions.
You want to know the groupings of sessions
and the lengths and how they sit in our dataset.
You can do that was the rank and over functions.
Let me show you.
It looks something like this, I'll just paste it in.
We're going to select the Top 100 sessions names
and the sessions length and we're going to get
their rank and we're going to rank them over the sessions by
order descending and we're going to call that RankByLength,
easy for me to say,
from sessions and we're going to order it.
What's going on here?
We're going to go get these things and we're going to use
our rank functions over and order by functions.
That means within the grouping of these,
get me the rankings and order them by length.
Let's run that and you'll
see what comes back when we look at that.
We see that the intelligent data platform has a length of
50 and it sits within the ranks as number 1.
Depending on what groupings we asked for,
we could see this is our grouping of the longest sessions and
the next longest and the next longest and it
would have those grouped up together.
The overall function of the statement gives the rank functions
the details on what we want to group the values by.
>> What is that as same as,
you've used it before in the self-joins lessons, I think.
>> That's called an alias.
You can alias any selection with
a friendlier name just by adding as to it.
For instance, we could use something here,
I'll show you it looks something like this and then paste that in.
Select name from people and I'm going to call the people
table p. Just a little easier to type where p.name.
If I've got really long names in my tables, this is handy.
Now I can do this and you may have even
seen me use it in joints and things like we
did earlier so you can see it comes
back and it just says p.name, so that's where that.
It's a little friendlier to work with,
especially when we start getting really
involved in some big joints.
>> Is a function something that I can use to start
aggregating data like counting things and so on?
>> Absolutely. For instance,
if you would like to know
how many sessions are at the SQL bits event.
This is something that would be a little
harder to do in your spreadsheet.
You can use the count function.
I use this all the time.
So let me show you this one.
Select count star from events inner join session to event,
event ID to session ID,
sessions, session ID to session ID.
This is the same thing we had before we just added
this value and we could put a where on here if we wanted to.
Let's just run this as is.
>> Hey, what's that asterisk doing there?
>> I probably should have mentioned that earlier.
If you want all the columns from a table,
you can just use an asterisk.
Since I'm just counting the rows,
I just used it to save typing,
sorry, should have shown that to you earlier.
Now for numeric values,
you can do more things than just those aggregates.
Let me show you some of the cool things that you can do here.
Let's assume that your boss,
you know how your boss is will sometimes ask you for
all these metrics around your events and so on.
What if we wanted to get all the sessions?
We wanted to get the average minutes per session,
the minimum minutes, the maximum
minutes and the total hours we submit.
Well, we can do that with various functions.
You can see all these aggregates come back very quickly.
>> That's pretty cool, I'd definitely be using that.
>> Now, when we've been going along by the way,
I just wanted to let you know the system
isn't doing exactly what we say,
but it is doing what we need.
>> Wait, what? What do you mean by that?
>> I'll tell you about that later.
For now, know that the select and
then the from and then the where statements are processed.
The system actually has a new table now it
builds that it can use, a virtual table.
You can work with the virtual table
before you see it with a couple of other functions.
The first is group-by.
This statement does what it says,
it groups the rows over what you get back.
Let's say you want to group all the people you have
in the people table by the type of person that they are.
You can use the group statement to do that,
here I'll show you a query that does that.
I'm going to select people type and then the people name from
people and I'm going to group the people by type and then by name.
If I run this, you'll see what comes back
and we can see that we've got our event contacts,
our speakers, and our speakers for Marisa Matthews,
and of course, you can add
a order-by statement there to sort them and all of that.
The key here is once again,
we're just building on the statements that you've already learned.
>> I'm following.
>> Very cool. After you group by operation,
you can further filter the data
that you grouped because of the way
the operations work using the having clause.
Let's say we want to group the people by type,
but only if there's more than one person in the type.
You've built this condition,
I want to go after the people. Here, I'll show you.
We'll just run a quick query that does exactly that.
Once again, we're going to get people and we're
going to count the number of people in the type.
From the people table,
we're going to group it by the people type,
this one right here,
but only where this pseudo table that we built here as we go just
as it's building up has account in
the people names of more than one.
We're only going to get people
back where there's more than one person in
the type and you can imagine all cool uses for that one.
You can see there's two people in the speakers.
You don't have to memorize this or anything,
but let me show you exactly,
I told you earlier that that's not what's
really happening when you ask for things,
let me just show you what's happening under the covers.
I'll paste it right over here in our query Window again.
This is what's happening under the covers when you send a query.
The statement actually processes the select statement this way.
It does froms then wheres then group bys,
then havings, then select, and then order-bys.
>> Well, I'm glad I just don't have to memorize that but I do
see that it can affect how I'm building these queries.
There can also be effects if I don't think
about how the system is processing the data.
[inaudible] I think as
you go try out some date and
other functions just to get the hang of it,
I'll head over to those by models.
>> Fantastic.