All language subtitles for [SubtitleTools.com] Data Aggregation - Learning Oracle 12c [Video]

In this lesson, we're going
to take a look at the concept

of data aggregation.

That is taking large
amounts of data

and using some
computational method

to arrive at a single value
from that group of data.

And to do this, we're going
to use multirow functions.

And multirow functions will
take a number of values

and return only one
value in return.

So that would be something
like, for instance,

and we'll look at how to do
this, how to take an average.

So an average takes a
large number of values

and returns one value.

And that's what
multirow functions do.

And those are an important part
of data aggregation in SQL.

Let's connect to our database
and take a look at this.

So we've got our connection.

Let's start with
probably the simplest

of the multirow functions.

Let's say select star from EMP.

And we have our rows returned.

Now, SQL Developer
gives this the benefit

of these row numbers that
tell us how many rows we have.

But if we didn't have
that or we wanted

to know the number of rows
that met a certain condition,

we could use the count
multirow function.

So here we're saying count
star, star meaning all the rows

from the EMP table.

We might change this
to dept, count star

from the dept table--

that has four rows--

or the bonus table,
which has only one.

So count star is going
to allow us to count

the number of rows in a table.

We could also do this
with a limiting condition,

where job equals clerk.

So how many people in the
EMP table have the job clerk?

There's four.

How many are managers?

Three.

So count is a
multirow function that

takes in all of that data in
the column, the job column,

and returns a count
based on that.

Let's look at another
multirow function,

which we mentioned
earlier, average.

So sal being the salary column,
and we can take a look at that,

here's the list of
the salaries we have.

Well, what's the average
salary in that list?

Well, we use the average
function to calculate that.

And notice that it brings
back a long decimal.

Here's where we could put a
single-row function in concert

to maybe the 2 spot, just
to shorten it a little bit.

Notice this brings in the
idea of a nested function.

So here we have the
average function.

And it's nested inside
the round function.

And we can do this
because average

will bring back a single row.

And round is a
single-row function.

So this calculates the average
salary in our EMP table.

Let's take a look at
another function that

works against the EMP table.

We'll look at sal again.

And we'll use the min function.

So min is a multirow
function that

looks at all the values
in the sal column

and brings back
the minimum value.

And so if we were to
look at all the data,

we'll see, in fact, that
800 is the minimum salary.

We can also use the max function
to find the largest value, that

being 5,000.

And again, there's
the max value.

What if we wanted to
know the total salary

for all of our employees?

Then we would use
the sum function.

Again, multirow function takes
many values, returns one.

So we're starting
to see how we can

use data aggregation to
answer some questions that we

have about the database.

And these are simple.

But they do show us how we
can use the SQL programming

language to answer questions.

Well, let's take this a
little bit further and add

a new clause to our statement.

So here's our data.

Let's say we want to know the
average salary of our managers.

Here's the average function.

Managers is in the job column.

So this shows us how we can
use the group by command.

So group by is a clause
in our select statement

that we can use to get
average salaries in this case.

So we're finding the
average salary for each job.

And we wanted to know manager.

And that's here.

And so we could apply a
round to that, if we wanted.

But that shows us that
the average salary

for each one of the
employees in our EMP table.

So the group by clause is always
after the from clause and the

where clause.

So we're actually leveraging
a data aggregation

function, a multirow function,
with the group by statement.

Let's see what else, other kinds
of questions, we can ask here.

Let's find the minimum
salary in a department.

We list out our departments
and then the minimum salary

in each one of the departments.

Let's say, for instance, that
we forgot about the group

by clause.

And we think, well, what I want
to know is the minimum salary--

I want to change it to
the maximum salary--

in the EMP table for
different departments.

Click this.

And we'll get this error, not
a single group group function.

And what that means
is this function

has no way of displaying
the data and this column

because it's not been
directed how to group them.

And that's why we need to
have the group by command.

We tell it how to group it.

We didn't tell it, in
this case, to group it

by the name, group
it by the job.

We showed it the column.

We said, well, we want
the deptno column.

But we never gave the specific
direction on how to group it.