Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.