query

Google Sheets QUERY Function Tutorial - SELECT, WHERE, LIKE, AND, OR, LIMIT statements - Part 1



Sharing buttons:

today we'll talk about query function at

least we'll start talking about it

because the function is pretty intense

and this will be just the beginning

probably the first one out of a series

because it's probably gonna be taking a

long time to explain so let's go ahead

and get started

what does query function do first of all

if you are familiar with SQL or what

some people call sequel then a lot of

this is gonna be pretty similar

otherwise just follow the videos and

you'll figure out what's going on a

query function can do a lot and in this

video we'll just cover some bare basics

of what it can do what I'm going to do

is just get started I guess it's the

best way so usually you use query

function when you have some sort of data

and you want to extract either a part of

that data or you want to do some sort of

aggregate operation on it so you want to

sum something based on something else so

think about like a situation of some F's

of pivot table if you have a reused one

of those functionalities it would be

sort of similar so I'm gonna go under

this tab called query and data I'm gonna

be using is whatever's on this tab so

I'm gonna go back to here and I'll go

ahead and get started with my equals

query function and the first parameter

is gonna be the data we'll be using the

initial data is gonna be this data on

the step so I'm gonna click here on the

end of this data then I'm gonna do ctrl

shift left and down arrow keys to

highlight my data so that's the data I'm

going to use I guess I'm just gonna go

ahead and hit f4 just for good practice

so comma and then the next one is the

actual query that you're going to run

which will be as a string so that means

we need to use quotes from both ends

of that and we'll get to that query and

that's where we're gonna concentrate

mostly but finally there is also the

last parameter which is how many header

rows your data has now in this

particular case if we look at our data

we have one top row that's our header

row so I'm gonna provide one for my

header row close by function and right

here we're gonna pass the query that

we're going to use so similar to sequel

syntax you're gonna start with your

select keyword I guess that's what we're

gonna call this and then we're gonna say

what we want to return as an end result

so let's say what I want returned as an

end result is the list of all brands and

the sales column so the brand is an e

column so I'm gonna say select E and

then comma and then the sales is my F

column so I'm just gonna stop at that so

I'm gonna select E and F columns out of

this data and I hit enter

and let's give it a second I guess and

there it is that's my data so basically

I'm pulling that data out of that tab

and I only picked two columns out of

that data by doing this so what I'm

actually going to do I'm going to take

this function out of this cell let's

clear this out and I'm gonna actually

place it starting here let's go ahead

and get to run and what I really want to

do the reason I did this is instead of

me going back to the actual function and

trying to retype the actual query I'm

gonna put my query over here and I'm

simply going to link to it from my

formula so I'll just say my query is

gonna be this which should get us the

same result this way I can just type

here without having to go back to the

formula every time to update it so far

we just extracted two columns so let's

say I wanted to extract all the Brandon

sales for the year 2016 so the year is

in our H column what I can do I can use

this where statement so I'm gonna go

back go back in here and I'll go back

here and use my where statement and the

column I believe I said was age and I'm

gonna say where H equals two and in

single quotes I'm gonna provide what

it's gonna be equal to so I'm gonna say

should be equal to 2016 and in this

particular case because it's a number I

should be able to actually just not do

those single quotes and say equals 2016

so let's go ahead and try that so there

it is so I was able to extract

everything that

is 2016 in this year now I'm curious

what's going to happen if I actually do

provide the single coats meeting it's

gonna look as text let's see if we get

anything so we don't

so obviously the turns out the type of

data matters so if it's a number 2016

you have to actually provide it as a

number without those single coats but if

we're looking for text we would have to

provide single coats so to give you an

example of that let's do something

that's not a number so this is 2016

that's what we have now let's say we

want to pick everything that from the

state of Illinois so that D column and

we want to pick everything that's

Illinois go back here and instead of age

I'm gonna say it D and it's the state of

Illinois so there we are let's extract

everything out of state of Illinois the

one problem is here we can't even tell

if it's actually extracting the right

thing since we're extracting Illinois

but we're not bringing that column in so

that Illinois column is column D so

let's also select debt as a result so

we're gonna do e F and D where D is

Illinois so we should be able to see

that everything in this column is a

little way so we basically we're able to

filter our data from here so we're

filtering the Illinois column we'll

picking everything that's Illinois out

of it and bringing come on I keep

hitting on this tab while I should be

tabbing here now let's say I wanted to

pick everything that's in the state of

Illinois right but also that's only 2016

so I want to gear as well so H column

here so I'm going to say where D equals

Illinois and age equals 2016 so again if

we were really trying to figure out

what's going on we could

just also add the called

age here so you can see that it's

actually 2016 so we're picking all the

cells that are in that state that we

want and the year that we want now we

could also keep expanding and do another

end so what if we want all the cells

that are in Illinois and in Indiana or

California or something like that

let's go to California Illinois in

California so I'm going to go here and

I'm going to say where D equals IL or in

this case D equals CA

so there it is now I didn't do that your

statement were just picking Illinois and

California now what if I wanted to do

this right but I just wanted the year

2016 so what I can do I can wrap this

entire thing in parentheses right here

so this should still work all right and

then I'm gonna add an and statement and

I'm gonna say and and I wish I

remembered the column but since we have

it here it should be easy so that's a

perfect column age so age equals 2016

and there it is now we're picking all

Illinois and California and a year is

2016 in those columns so that's so far

equal statements so what if we want to

pick all the cells that are above a

certain number so we want to pick all

the cells that are greater than with

numbers we have here I don't know I'm

gonna try $500 so the sales call them is

a column F so this the second column

here so F is greater than and I said 500

so that's not gonna include 500 but

anything above 500 should be in there so

there it is now we're picking all the

numbers that are greater than 500 we

could obviously continue this and I'd

say and the year is 2016 or we could

also say and H is greater than 20

sixteen right so everything that's

greater than twenty sixteen which is not

gonna include 2016 in it so you can see

you get all 27 teens here so if you also

wanted to include 2016 we could say

greater or equal to 2016 so now we have

two 2016 2017 and everything that's

greater than 500 in there you can also

sort your results so let's say we wanted

to sort by sales numbers and we wanted

the highest sale on top or lowest sell

on top so you do this by adding an order

by statement so we're gonna order by the

sales column so the sales column is f so

again it's not column here it's the

column of our data right so so that's

the F column in there so now we have

this data ordered now you can see that

the regular order is going to put the

lowest value all the way on top the

highest value all the way down so we can

change that by adding descending in the

end so now we're ordering by column F

and the highest value is all the way on

top let's simplify this just get to just

years 20 I'm gonna just get this 2

equals 2017 ordered by sales number so

we should still get that highest sale

number there but we now should have more

results because well we didn't filter

for sales that are greater than a

certain number right so we're gonna

still order by F so let's say if you

wanted to get the top 10 sales in 2017

and you don't want to get all this

results right we just want to get the

top 10 we could

also use a limit close in the end so we

can say limit 10 so again if you are

familiar with SQL none of this should be

new to you so that should make complete

sense so that's it top 10 results from

2017 highest sale right there

right and if you wanted to get the

lowest 10 results instead of sorting

this descending we could just do our

regular sort which will be our ascending

sort so there it is that's our lowest 10

numbers out there so you can use your

we're close ordered by to order your

data where to filter your results and

final select you select which column you

want returned okay so let's move on so

the next one let's see if I can find a

good example to do some like statements

yeah we should be able to do this so I'm

going to do with some like statements so

you can see how that works and usually

that works if you're trying to find some

text that contains something so let's

say I want something that includes

Western in here so that's essentially

its gonna pick Midwestern and it's gonna

pick what else just Western I guess so

both of them so I'm gonna go back here

and I'm going to say where and should

probably return that column so that's

our C column so let's make sure we

return that column as well because

otherwise stuff gonna make a lot of

sense so I'm gonna so we can't see and

compare and I'm gonna say where C is

like

and here we have to provide that pattern

so I said Western right that's what I

was looking for so if I just do Western

that shouldn't return anything because

that should be kind of like saying equal

to but the way you can use the wildcards

before and after so I can use this

percentage sign before and percentage

side after my Western and you should see

that what we have returned is a lot of

midwestern here so if I go back you'll

see that we've got Midwestern but we

didn't actually get Western because

Western is has a capital W in there so

if we switch that to instead of Western

we can do a Eastern I guess

so we can see how we return Midwestern

we did Western as a result because now

we're not looking at that first

character and you know it's matching

that now what you can do you can do

something that starts with something so

if you do mid and then you do this

percentage sign you're saying that it

has to start with mid no characters

before that and then it can contain

other characters after mid so they

should match our Midwestern as an end

result so that's our like so technically

we'd like you can do two things so you

can do either this percentage sign so

that's our wildcard and you're basically

saying it's zero or more characters on

that site where you put the wild card so

you can do wild card before and you can

do wild card after and that's kind of

how your wild cards work there now you

can also use underscore characters I'm

trying to find a good example to use

underscore on it probably our data is

not best example so actually I'm going

to use this K column that should work

just fine so let's go ahead and return

that so instead of C let's do K and I'm

gonna say that the K is like so we can

do 2015 and then we can do our wild card

character so that's going to return

everything there starts with 2015 in

there right so there this is 2015

everything in this GS date column is

being returned now what if I want 2015

any month

the seventh day so I want any month the

seventh day get back here I can modify

this and I can use this underscore

character so this means exactly one any

character in there so we're saying it

should be 2015 then one character after

that and another character after that

and then it should be zero seven so see

what we get there it is so we got 2015

zero five zero 7 2015 6 0 7 everything

that ends with zero 7 starts with 2015

so if I did something like four

underscores here it means get any six

characters that end with zero seven

after those six characters so that's our

like operator so I think I'm gonna end

this video on this and then on the next

video I'm going to talk about another

operator where you can use regular

expression to actually extract matches

using your query function