In this video
we will try to understand
what are aggregate functions
what is the use of group by clause
and how to use the having syantax
We have a very simple table here which has country name
population and year
so you can see we have India
and for the year of 1990 India
had the population of 1000
We have Nepal and for 1990 Nepal
had 200 population
For the year of 1991 India had
1500 population
You want to go and find out
total population
across India Nepal and Sri lanka
all the population across all these countries
you can go
and use
one aggregate function here called as sum so you can
say here sum
population
and we execute this
you will get the total population
across all the countries
sum is nothing but it's a aggregate function
You want to find out
the maximum population
Say here max of
population
max of
population
and if you also wish to see the minimum population
you can say min so these are all
aggregate functions
In the same way
I can use here
min
and
population
If I fire
this now you can see
I have three fields here
the first one which actually shows the
total population
The second one shows the maximum population
and the last one shows the minimum population
in the same way if I
wish I can also see the average of
populations across countries or
across total number of entries
so for example I can say here average
population
whenever I
use this aggregate functions like average it is
very important to also look at
what is the number of count so again there is a count again
one more aggregate function here
This will give us the total number of records
so if I now execute
the second last column is nothing but it’s a average
There are lot of readymade aggregate
functions which are given by SQL
Server. In order to see that what you can do is
go to your data base so for example my
database currently is personal details
you can see that there is a node
here called as programmability, expand that
There is something called as system functions here
expand that and then
expand the aggregate functions node here
so you can see we have
already used average we have used max
we have used min we have used count
we have used sum
There are lots of other
readymade aggregate function
For example standard deviation
grouping, count
big check sum etc
so I will not go in details of
all of these aggregate functions here but I have
covered very important
once like sum,
max, min, average and count
so in case you wish to
experiment with other aggregate functions
you can start using them
and when you move your mouse right you can
see there is small tool tip here which actually
tells you what that aggregate functions does
If you look at
Stdev it actually says that it actually gives
you a standard deviation of all the values
which are given in that table
I will not be covering you know the other aggregate functions
but if you wish you can
try to experiment them and try to see
how you can use them
We can use aggregate functions
and we can display
data on the screen
I want to
go one step further
By using the sum aggregate function
we can display the total population across
all the countries
You want to see this population country wise
or you want to see this population grouped
country wise
You can say here
country name
display this sum
grouped by
country name
By using this group by clause
it will display this sum
as per India as per as per Nepal and as per Sri lanka
If I execute this query over here
How he showing me total population
grouped as per country
He is showing me India has
4500, Nepal has 1200
In other words by using this group by clause you can logically group
your aggregate function data
into proper logical groupings
also add more groups to the group by clause
If you remember we had something called as
year. I can say here
group the sum
by country name as well year
If I execute this now it shows me some of population by country as well as year
One golden rule of group by clause is that
whatever columns you want to go and display in your select statement other than the aggregate function, should be a part of group by
If I want to display my year
on the select
it has to be a part of group by if
I don’t do that and if I go and fire this select statement it will say that
It will say this year is invalid
because it is not contained
in the aggregate function or in the group by clause
Whichever columns
you want to display here
should be a part of group by clause
Either they are part of the aggregate function or you know they should at least be the part of group by clause
If I want to display here year, year should be a part of my group by clause also
Many times you would also like to go and filter records depending on aggregated value
Remove this year for now
we are seeing sumas per country
I want to display records
whose population is greater than 1000
Many of our
SQL friends think about is
Where sum of
population
is
greater than 1000
This how we normally write syntaxes
but if you execute this you can see we have got a error over here
It clearly says that
a aggregate value cannot appear in the where clause
We cannot use the where clause
to filter aggregate values
For that use
the having clause
Sri lanka has been excluded from our record set and we are only seeing India and Nepal
Whenever you want to filter
as per the aggregated values you cannot use where clause you have to use the
having clause
I hope you enjoyed this video
In this video we
tried to see what exactly are aggregate function then we saw how group by adds more value to the aggregate function
and then we also saw that how we can filter
records of the group by clause by
using the having keyword
thank you so much
Whatever video you have seen
is just a glimps of what we have done
so in case you are interested in our video package
go to our site that is www.questpond.com
you can call on this number and
you can ask
the complete DVD package what we have
so in this DVD package what we have done is basically we
have covered almost everything
what a .NET developer wants
so right from basics of
ASP.NET objected programming SQL Server to
new technologies like WCF
Silverlight Linq Azure entity
framework we also have
UML architecture estimation
project management that it is complete
invoicing package invoicing project end to end which is covered
so that you can better feel of
how to actually create projects in a
systematic manner we have
cover server products you know both for sharepoint
2007 as well as 2010
we have lot of best practices videos on
SQL Server etc so this complete
package you know you can get from www
questpond .com if you are interested
and you can call on this number and
you can ask for the rates it’s
very decent rate what we have
in the same way
you know as
compare to the videos we also have one more
products with us that is our interview questions
books so we have different kinds of interview
question books you know right from .NET
interview question to SQL Servers interview questions
sharepoint interview questions
BizTalk interview questions etc
so in case you are interested in the books part
you can call on these numbers
as per your location so
you can see this numbers on bored at this moment
so I hope that
you keep enjoying the videos you
keep seeing our
site and I hope you gain more knowledge
thank you very much