so there are a couple of things you can do here so let's say i want to do query function to get
the average price so if i just do a quick query function here we'll take this data
over here ama i'm going to write a select statement so i'll just do select
avg price which is that b column for now i'll do this and that's it and we'll do one for our header
being the first row up here but right now if i hit enter that should give me the average 35.75
to make this a little more interesting i guess let's add another column here we'll do category
and uh in here we'll basically do like drinks
and fruits
and i think this is case sensitive so i'm gonna make both of these fruits lower case
so i could now take this and go back and add this extra c column to this
and if i add the c column now i could group this by this category so i'm going to add that
category here too i'm going to do c and average b and then i'm going to group by
that c column so at this point it should give us the average by category of drink we got
i'm actually going to add just a little more data here so let's actually just open
reference this so we can just keep adding data to this i'm going to remove that five out of there
so let me just add a couple of things that's because of that blank now i get a blank too
that's fine but i will add a couple of more extra stuff here i'll do like rice
and we'll do i don't know flower or something
we'll just do cook and we'll do some sort of prices here too
as you can see we got this average per category now what if you want to now filter this
to only get the ones where the average is above six so basically that would not include cook
that would only include drinks and fruits now you can't really do that directly in this statement
right here so what you have to do you have to sub query this query so what that means is that
basically just think about these results as your data now so if i do another like query function
right here and at this point i'm gonna select this data table and i'm going to say basically select a
comma b and then i'm going to say where b which is that average is greater than
whatever that number is so let's say we want to do greater than 10.
and then i can do headers as one row so apparently that gave me an error let's
take a look and see what the problem is so oh actually it's not a and b it's e and f
column names up here and then i'm going to do f greater than 10. so you can see how that filters
this to that now what we want to do we want to basically combine the statements together and
what that means is basically i'm going to have to take the results from this and put it here
as the data so i'm going to grab that without equal sign copy this hit escape go back to my
second statement and just paste this over here now when i'd enter this is not going to work
and the reason it's not going to work now because this new query i have here it's an array it's no
longer a range therefore this e f those things don't exist so what we would have to do for the
second one we have to refer to these things by their column numbers so the category would
be column one average would be column two so what we would do here we would do col1
and col2 and then we want the col2 to be greater than 10. and now we should have
one statement that does both if i erase this you'll see that this works by itself
and that's pretty much how you would do filter by the average