Tutorial 30- Aggregate Functions in PostgreSQL(Advanced) Video Lecture | Postgresql: A Complete Tutorial - Database Management

hello guys welcome to another tutorial
PostgreSQL in this tutorial we'll talk
about some advanced aggregate functions
in the last tutorial we talked about
some basic aggregate functions that are
min Max or some average and count star
in the shooting we talked about some
advanced aggregate functions also about
some statistical functions that that
command or the aggregate functions only
so the first idea function I'm going to
talk about is the array a cricket that
is a first of all we should be with the
data that is select star from company so
as you will see that this is my data of
nine rows so what array aggregate does
is that it activates the value inside a
normal array like suppose I want an area
type data and I want all these rows to
be aggregated into some merit like I
want the names column to be aggregated
into an array field so for that we have
the aggregate function so what you have
to do is select array underscore AGG and
name if I want to put all these names
into an array so I will just use array
underscore vgg name from company
so you would run this and you see that
this turns out to be an array of all the
names down so similarly the second
function is the jason underscore
aggregate or jason underscore AGG that
aggregates all the names into JSON
format so now as you as you as you saw
that this is an Arabic format now if you
write JSON here you would see that it
would encode it in a JSON format so this
is the way that we aggregate normal rows
into array or JSON format so the next
thing is that if you want a key value
pair as in a normal Jason so for that
you have for JSON object aggregate so
what it does is that it creates a key
and a value for example I want to create
a key that is the name of the user and
the value would be the age of the user
so for that I will use jason underscore
object underscore AGG and the name is my
key and the age is my value so you will
see this gives me an object so you would
see that this gives me a key value pair
or Jason for my data that is hina air is
the key and 16 is its value suppose and
the similarly that the name here is the
key and the age is the value of each
Jason type data so this was about the
iris and the jason aggregates now i'm
going to move on to some statistical
functions so statistical functions
include the like you could find the
standard deviation of a particular
or dataset or standard deviation or
particular row if it is in the numerical
format for example for the standard
deviation you could write s ad AV s TTB
Guinea that is or s T TD e and H is my
numeric type data here so this would
give me the standard deviation of the
age if you don't know what standard
deviation is probably you should google
it first and using the standard
deviation here is eight point zero one
similarly we could find the variance of
some numeric type Rose just write
straight away variance and I will see
the variance of the H that comes out to
be sixty four point two five so this is
the way to find the standard deviation
and variance of email type data in our
database so one more thing that I'm
going to talk about is the mode mode is
basically a particular number or a
particular string that repeats the most
number of time or in a particular data
set so to find the mode we have some
different method we need to you within
group clause for finding the mode I just
demonstrate a single function that is
the mode function that is available in
this class so you could just write
select mode and so right within
group and you need to order by the
attribute you need to find the mood of
like I want to see that which H is
repeating the most number of times so I
will order it by age and it would just
run it and you see that number 34 is
repeating the most number of times so
you could just check it out add a select
star from company and you see that 34 1
2 & 3
yeah so 34 is repeating 3 times so that
is why the mood is 34 for the attribute
age similarly you could see it for
salary like I could see that the 4,000
is being repeated thrice so it should
show me 4000 get all of this and yeah so
it shows me 4000 so this was all for the
advanced aggregate functions so thank
you for watching this tutorial subscribe
for more and stay tuned