Group by in sql server - Part 11 Video Lecture - SQL Server Administration

hello welcome to presume technologies I
am venket this is part 11 of sequel
server in this session we'll talk about
how to group rows using group by
filtering groups and the difference
between where and having clauses now if
you look at the definition the group by
clause is used to group a selected set
of rows into a set of summary rows by
the values of one or more columns or
expressions it is always used in
conjunction with one or more aggregate
functions now what are the different
aggregate functions that are available
in sequel server there are many for
example we have some average count
minimum maximum etc let's look at them
now if you look at this table I've got
the salary column and let's say I want
the total salary that I am paying within
my organization so obviously what will
you do if you have to do it manually you
will take each row and add them together
but in sequel server we have an
aggregate function called sum which we
can use
so select sum so what do you want to sum
I want to sum the salary so salary from
tbo employee so all you're saying is
okay total this salary for me so that
gives us the total salary so we are
using this aggregate function similarly
if you want to say let's say for example
I want to find out what is the minimum
salary that I am paying you know within
my organization if you have to do it
manually you'll have to inspect each row
and see okay 4500 is less than to the
greater than 2800 okay I think 2800 is
the minimum salary but in sequel server
we have the aggregate function which we
can use minimum okay I want the minimum
salary from this table so when we
execute this we should get the minimum
salary and along the same lines we have
the max function as well which gives us
the maximum salary
so max of salary so when we plus that we
get that okay so like this we have
several aggregate functions in sequel
server so if you look at the definition
you know it says group by clause is
always used in conjunction with one or
more aggregate functions okay now let's
understand blue by so if you look at
this table I've got a an employee table
which has got their names genders salary
and the city they are working in okay
now I want you to write a query let's
say and that query should give me the
result that you see on the right hand
side here I want the total salary that I
am paying to my employees by city okay
how do we achieve this using group by so
if you look at this from a you know if
you have to do it manually okay what
will you do
you will take each London record and
then add their total salary similarly
you take each New York record and add
their salaries okay
so you first group the records by city
and then sum the salary column across
that group so you and the query has to
do that so that's why we use group by
clause here so let's see how to use that
so if you look at the query that we have
written all the day we are using this
sum aggregate function to total the you
know to sum up the entire salary that we
are paying to our employees okay now
this is the total salary of the
organization but I don't want the total
salary of the organization instead I
want the total salaries paid by city so
in our output we not only want the total
salary we also want the city okay
so in our select list we need to have
the city as well as the total salary now
if you look at this for the total salary
we didn't have a column name so let's
give a column name by using AZ and we
will say total salary so this is an
alias name for this column cool so now
let's okay look at this select City sum
of salary from TBL employee
okay you're saying I won the city and
the total salary but you should also
tell I want to group okay by city so
group by city and then calculate the
total salaries across that city okay so
when I execute this query you should see
the same output so the city names and
the total salaries let's see what's
going to happen if I remove this group
by clause and then execute this query
now by looking at itself you should you
know it should make sense you know this
is going to give an error because you're
saying okay select city column okay I
select the city column and select the
sum of salaries okay so you're saying
you want city column and the total
salary but you are not telling the query
to group them by city and then total the
salaries okay if I don't use the salary
city column then it makes sense okay I
want the total salaries in the entire
table but when I use the city column you
should tell it okay group by city as
well so obviously if I execute this
query you will get an error stating that
TBL employee dot city is invalid in the
Select list because it's not contained
either in an aggregate function so we
cannot apply an aggregate function to a
text column you know what does it mean
when you say some cities you cannot some
cities London New York okay similarly
you cannot find the average of city
names itself okay it's only on numerical
columns you can apply the aggregate
functions like sum average etc okay so
we cannot use an aggregate function like
sum average on this column and it's not
part of group buy as well that's why
it's invalid in the Select list okay so
that error makes sense okay so the
columns that you use in the Select list
okay they have to satisfy one of these
two conditions you should either have an
aggregate function applied on that
column or that column should be part of
group by clause
if none of these conditions are met then
you get this error okay so the moment we
put city in the group a clause
this condition is met and we don't get
that error and that makes logical sense
as well we don't have to really mug and
remember that it makes sense you're
asking okay give me the total salary and
the city column but you're not telling
it to group by city column so how will
sequel server know how it has to total
those salaries all right so obviously if
you omit the group by clause and try to
execute the query you get this error and
we have seen this so if you look at in
the output you know we are get we are
retrieving total salaries by city but
let's take this one more level not only
by city I want to break it down further
by gender so if you look at the next
slide look at this I want the output
like this okay in the previous example
we are we are showing the total salaries
paid by city but now I want to break it
down even further by gender as well for
example in Sydney how much is the total
salary that I am paying for male and
female employees so if you look at the
example here for male employees within
Sydney we're paying 2800 for female it's
4800 so I want this breakdown as well so
we are grouping now by multiple columns
okay so we are grouping this total
salary first by city and then by gender
okay so how do we do that it's pretty
simple if you look at the output itself
it should make sense okay in the Select
list we should now have three columns we
already have city and total salaries
columns we want the gender column as
well so let's put that in the Select
list so I already have city I also want
gender so select city come agenda now if
I execute this query without any further
modifications let's see what what's
going to happen you might you know you
should have already expected we get the
same error that we got before gender is
invalid in the Select list because
you're not applying an aggregate
function on top of it or
and it's not part of the group by clause
so we get the same error here so gender
is invalid in the Select list because
it's not contained either in an
aggregate function or the group by
clause so we need to make it part of the
group by clause to tell sequel server
engine alright I want you to total the
salaries first by city and then by
gender because we want that breakdown in
that order so when we execute this query
you should see the output that we have
here you know it's slightly different
here the cities are together for example
if you look at Sydney Sydney they're
next to each other New York New York
they're next to each other but if you
look at our output it's not so so
obviously we use the order by clause to
order the result and if you're not sure
on what is order by class I will
strongly encourage you to watch the
previous video in this series so order
by city so when we execute this you know
it should match the output on the
presentation all right now you might be
wondering okay I have by city by gender
breakdown let us say I want by gender
and by city you know probably something
like I want first may be gender and then
maybe City so I want first by gender and
then by city in this order you just
changed the order
and execute this query we have the same
results but the columns have just
switched okay first by gender and then
by city cool okay now let's take this
even more you know to the next step now
we have seen we can group based on
multiple columns let us see if it's
possible to use multiple aggregate
functions absolutely you know here we
are showing total salary by city and by
gender let's say I want total number of
employees as well
now if you remember if you want to find
out the total number of employees we can
use let's say if you look at this TBL
employee table we've got ten rows there
and if I want to find the total number
of employees within my organization I
can say count of star from TBL employees
when we plus that we get the total count
which is ten and for performance reasons
instead of using star use a specific
column name
you know maybe ID so I get the same
result okay so so what is it that we
want
we not only want total salaries by CD
and by gender we also want the total
number of employees so if you want the
total number of employees we use the
count a great function so count off ID
and if you want to give a meaningful
name we will say total employees okay
and when I execute this you should see
we have total employees as well okay so
total salary by gender by city and total
number of employees and you might be
wondering why did I use the square
brackets here if you want to have a
space in the column name then you have
to use the square bracket otherwise you
get an error so if I remove the bracket
okay we get an error because it treats
these two as two different words but you
can you know if it's just one word you
don't have to have a square bracket
around it so if I execute this we end up
with the same result
all right so we have seen how to use
multiple multiple aggregate functions as
well okay now let us see how to filter
the groups okay now so far we have seen
okay we got all the result but let's say
I want to see only male salaries I don't
want to see female salaries so how do I
filter that okay
now usually to filter the rows we can
use the vert class you know where you
can say gender is equal to meow so when
I execute this query what is going to
happen it's going to show us only the
male employees total cell reason and
total number of male employees okay the
other way to get the same result is
instead of using the where clause we can
use having clause but having clause
should come after the group by okay if
you remember select star from table name
there okay so after the from clause we
usually have the where clause but having
clause has to come after the group by
clause so now having gender is equal to
male so when we execute this query we
end up getting the same result now let's
see what's the difference between these
two queries you know here we said having
where gender is equal to male and in
this query we said where gender is equal
to male and if we execute them together
you should see we exactly have the same
output but the way that query gets
executed is little different you know
when you use where Clause the tape from
the table only male records are
retrieved and then they are grouped okay
but whereas in this when you use the
having clause all the rows from the
table TBL employee are retrieved
they're grouped by gender and then only
male groups are shown filtering female
groups so here aggregations are done for
every row in this table whereas here
aggregations are not done there they are
filtered even before the aggregations
are done
only mail set of records are retrieved
from the table and then aggregated and
then drill but remember it's possible to
filter you know the output either using
the where clause or having clause but it
depends on how you know how you want the
output so you can either use the where
clause or having clause okay from a
performance standpoint you cannot see
that one method is less efficient than
the other sequence of optimizer analyzes
each statement and selects an efficient
way of executing it and as a best
practice use the syntax as syntax that
clearly describes the desired result try
to eliminate rules that you wouldn't
need as early as possible probably using
the where clause okay
so now we will talk about the difference
between where and having clause now you
might have already understood one
difference by now where clause filters
rows before
aggregations before grouping sub
performed whereas having Clause filters
groups after the aggregations of the
formed okay and another difference is
that you can use having clause only with
the Select statement whereas where
clause can be used with other than
select statements as well like insert
and updates as well and aggregate
functions cannot be used in the where
clause let's see this okay what do we
mean by aggregate functions cannot be
used in the where clause examples of
aggregate functions we already discussed
count average some minimum etc okay so
let's say select star from TBL employee
where let's say sum of salary greater
than 4000 can I do this absolutely now I
mean you can do that there is a way to
do that you know using a sub-query and
having clause which we'll be talking
about in a later session when we talk
about sub-queries okay but in general
you know you cannot use aggregate
functions with the ver clause where as
aggregate functions can be used in the
having flow
and that's what we have seen until now
for example here you know when you're
filtering you know you can say having
maybe some off salary greater than 5,000
so when we execute that we don't get the
rules where the sum of salary is less
than 5,000 less than or equal to 5,000
so you can use aggregate functions with
the having clause but you cannot do that
with the where clause those are the
differences between weight and having
and this is one of the common interview
question that's asked these days and
it's very simple as well on this slide
you can find resources for asp.net
c-sharp interview questions that's it
for today thank you for listening have a
great day