The GROUP BY clause of the SQL SELECT statement (for ORACLE, MySQL, SQL Server, etc.)

The Structured Query Language (SQL) is the most powerful database language in use today. SELECT is the most powerful statement within SQL, and the GROUP BY clause is part of the reason. Learn the inner workings of GROUP BY and make yourself a more powerful database professional! From Steve O'Hearn of databasetraining.com and Corbinian, author of Oracle Press books, including the first "expert" level certification exam guide from Oracle Corporation, the "OCA Oracle Database SQL Expert Exam Guide: Exam 1Z0-047". (http://www.amazon.com/Oracle-Database-Expert-Exam-Guide/dp/0071614214/ref=sr_1_1?ie=UTF8&qid=1371769056&sr=8-1&keywords=oracle+sql+expert)

This information is good for use with Oracle, MySQL, Microsoft SQL Server, and any ANSI-standard SQL database.

Full transcript available here: http://databasetraining.com/node/3

---

Closed Caption:

hello and welcome to the people selected
the group by clause presentation this
presentation is going to look at how the
group by works how it is internally
processed and what you can do with it
my name is Steve o'korn with database
training.com and coordination and i am
the author of a couple books from oracle
press including the most recent sequel
certified expert level exam guide if you
want to become certified expert in SQL
my book is the one you would get for
that also been on oracle TV I've i'm
behind the Cartesian product challenge
in case you've heard about that and i'm
also a formally recognized bonafide news
source by the national press club on
issues of information technology
including data analysis predictive
analytics and related technologies we're
going to be looking at the structure
query languages select statement group I
with an esky well there's a number of
different subsets of languages and
within them in particular is DM our data
manipulation language DML includes for
specific statements the Select statement
is easily the most powerful and it is
within the Select statement that we're
going to be looking today we're going to
be specifically looking at of all the
clauses in the selected there's many are
going to be looking at group I in
particular so that's our frame of
reference
let's take a look what group why does is
it takes a set of rows that are the
focus area of a given select statement
and on aggregates them subsets them
together according to a criteria to
specify we'll see how that works
once those sets of rows are set aside
and are our subset adar are grouped
together
then you can perform a function on each
of the group's producing one row of
output for each individual group of rows
so no matter how many rows you may start
with your the output of your group i
could be anywhere between that and a
single row let's take a look here so
let's look at our sample database we're
going to start we're going to play with
here we made up a table called members
members has four columns and and I just
slapped together some data here you go
get nine rows we've got nine members in
our little sample database
each one has a last name each one is
assigned to an office
each has a certain number of hours let's
say these are ours budgeted for a given
year for each person to work the
business challenge we are looking at is
going to be to look at the average
number of hours for each office so what
we're looking at here is an average
number of hours per member but average
according to office we don't want to get
a single average for all nine rows we
want to get a subset of averages 14 each
office and the average number we will
ultimately calculate will be $MONEY per
member so therefore we're looking at the
rows of members each of growing equals
one member and that's what will be
averaging but the calculation will be
done to determine the average for each
office for example we have a couple
members of the president's office we
have some in the finance office we have
some of the publishers office we want to
get the
averages by office and have that be our
output so the answer is of course a
group i and group by statement by klaus
wille grew by the office and by naming
the office column we don't have to
specify the values that are contained
within we have no idea necessarily
what's actually in what you know what
the data values are we don't necessarily
know there's pressure financer pub or
whatever could be operations can be
anything we don't really care
we grew by the office week we specify
the column name from within the table
and that'll do it so step one for
internal processing of with the database
will do once we send this statement to
it
step one is that it will internally
group The Rose according to office
values so in essence it will sort the
rose
according to the values that are found
within the office column once we do that
we're looking in the sample that will
end up with three different groups three
different groups for each of the values
here and that's just simply because of
the nature of our sample data there
could be any number of groups of depends
on what's actually found with them so
once that first stage is done once the
rows are sorted next thing to do is to
actually calculate the average and in
this case we're going to be calculating
on finance and then for the president's
office and publishers office so that we
have our values in that set by doing
this we were able to in essence
performed two steps with a single state
the single select statement the group i
was very powerful feature the couple of
issues that we need to keep in mind
though the columns you can select your
select statement or limits that you can
just select anything we are using a
group by clause you can without the
group by clause possibly i mean probably
running a select statement stop by like
two groups and things will then suddenly
you're not able to select every column
you can only select columns that are the
subject of the group in our case you
know we grouped by office and or you can
select columns that are the subject of
an aggregate function so you can average
you can do other
other aggregates will take a look at
those but those are the only columns
that you can select from you cannot
select others and the reason is it
doesn't make any sense because if
there's going to be a single value
representing each set of rows that fall
within the group
there's gotta be a logical way in which
you can choose one value for that set
the database is not going to invent data
or draw some research conclusions on its
own you've got to be very clear
so as you can tell for example in this
sample data we're looking at among the
finance rose there's no way you can have
a single last name value represented for
that group that set of Finance rows of
the press seven presidents row center
publisher as you can't but we can do it
for animals because we're averaging
we're not showing the actual 120 hours
for the first row finance or Smith or
the that we're not showing the Browns
numbers or 9909 we're showing the
average so we're calculating a single
value for that set of rows and that's
what we're doing if the aggregate
functions that are available to us are
shown here we've got five of them and
don't forget you actually can use
minimax and the count function with more
than just numeric values of us were
familiar with these aggregate function
sometimes forget that she you know we
think well we can only use these with
numeric values
well that's not necessarily true you can
average Americans so of course you can
summon Americans and you can do that
can't some text you can average dates
but you can count occurrences of values
that contained within your container
with a numeric column or text column
deep help and you can determine the
maximum of the minimum
value that is contained within a set of
rows as well so and as you see in the
Astro footnotes here max when looking at
text is looking at values that move
towards z the latter part of the day off
of that whereas a min value would be
more like a and a min or max dating men
dates or earlier max states or later so
so there we go i said this has been fun
thank you very much for a pay attention
here drop me an email sometime and look
forward to the next video