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

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

Channel: Learn Google Spreadsheets

Views: 477,920

Keywords: QUERY, Google Sheets, Function, Tutorial, SELECT, WHERE, LIKE, AND, OR, LIMIT, statement, learn, sql, extract, data, filter, contain

Id: bW6P2YvLyZg

Length: 19min 29sec (1169 seconds)

Published: Sat Jul 01 2017