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

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.