Google Sheets QUERY - SUM, AVERAGE, COUNT, GROUP BY Aggregate Functions Tutorial - Part 5
HTML-код
- Опубликовано: 5 авг 2017
- Video tutorial series about QUERY function in Google Sheets.
In this tutorial we'll cover how to use QUERY function in Google Sheets to get aggregate data using SUM, AVG, COUNT functions and Group By Statement.
Google Sheets
www.google.com/sheets/about/
Website:
www.chicagocomputerclasses.com/
SIMPLICIDADE NA AULA!!! MASSA!!! Essa função query é poderosa!!! manipulação de dados incrível !!! Vou refazer toda minha planilha com esses métodos do query.
Parabéns Excelente aula !!!
No comments? Wow! This is very clearly stated; easy to follow. Thank you. ~ Subbed.
Thanks so much! I've been trying to do this forever!
All your videos are great!!! Clear, easy to follow and informative!
Great to hear!
This is actually cool that you can use SQL in Google sheets but I find that pivot table is way easier to get the same results. Great video!
Clear, quick, and well done. Thank you for the great video!
Thanks!
can you re-label sums and averages and count headers like you can with standard Query functions?
Great video! I have a dataset with station codes, location, date and measured values of dissolved oxygen (DO), ammoniacal nitrogen, etc. Is it possible to extract the average value of DO by month? I need to correct some cases missing, and, due sazonality, the month is something important. I only managed the avg of date range between years. Greetings from Brazil! Thank you!
Great video, thanks
seus vídeos são incrivelmente didáticos
The Best!!!
Great set of tutorials! Would love to see a query with the LAG function if that would work
Great work!
This is amazing
Great tutorial Many thanks
What in you need to have in the second tab the list of every day of the year and in each day see the sum of the sale that match that day?
Thank you! Your video saved me! I missed that "D" in 5:55 after "group by" in my work and everything goes wrong. Thanks a lot!
p.s. I wrote ="select C, D, count (E), group by C" and it returns "#VALUE!", annoyed me for a while..
is there a way to SELECT sum(F) without the column header "sum" popping up above the returned data?
Very helpful! What if I want to see the State that had the max sale in all regions in a table that only returns each region once and the state that sold the most?
Having to group or aggregate each select is not producing the results I want.
Region | State | max(Sale)
Midwestern | IL | (highest sale in midwestern)
Northeastern | MA | (highest sale in Northeastern)
etcetera
Very helpful!
thanks. it was a nice video. I am curious about "group by" . does it only work in english? actually I have dealt with some data with korean letters. but it didn't work at all. I am sure that there is no error in syntax. I did same things what you explained.
is anybody have the experences how to concat multi cols to one col in query?
Had a quick question on Query ....I have data in which one column has number format as " AB12234" when I use query function it's gives put as " " can you please provide resolution for this .... thank you
Hi,
When I add it between two columns and on one of the two columns, there are empty cells, the calculation is not done
Can you help me plaise, thank you
thanks you for your tutorial, could you show me, how to make where statement in googlesheet for more than one criteria or base on range criteria, like statement where in('a','b') for sql language
I need but not work...
=QUERY('names'!$A$2:D; "SELECT A, Sum(B) Where (A '') AND Sum(B)>0 Group By A, D"; 0)
SHOW ERROR: CANNOT_BE_IN_WHERE: SUM(`B`)
It doesn't allow putting SUM() in the WHERE. Is there an alternative to achieve this? I want to pull values that are greater than 0 and I don't want to pull empty cells that aren't empty
lets gooo
please produce more about the group by date, month, product, and subtotal.
Can countif works with query?
If I wish to select sumif(credit), sumif(debit) credit and debit are type in same coulmn
how about if we add column to count distinct brand ? how to do that ?
🙏🏻
=QUERY(BIGTHANX,"SELECT B,E,F WHERE E > 'THANK YOU ",1)
Nice!
Can we use these aggregare functions with WHERE clause or HAVING clause?
Hello I am using this =QUERY(AMAZON!A:AA,"SELECT C,D,N,P WHERE C = 'Order'",1) but I want to add N and P when data import
I want to get results by searching for keywords (be it upper case or lower case), in various columns. Maybe it will find the keyword in column D, maybe F, maybe G... How can I do that? Currently, I use various IFERROR, FILTER, ISNUMBER, SEARCH in just one formula. I am guessing it can be done through QUERY, but I still do not get it.
I'm looking for guidance on how to pull out the following
I want to "select A,H,I,J,K,L,M,N,O where G contains 'HDW'" but I also want to SUM(M) and GROUP by L
If I write as follows
Select H,I,J,K,L, SUM(M) where G contains 'HDW' group by L,K,J,I,H
It doesn't do the group by correctly. I end up with duplicates of some of the items still. Any help would be great.
Your query looks correct.
@@ExcelGoogleSheets
Thanks for the response.
It returns duplicates of L still. It has something to do with the group by L,K,J,I,H. This is somehow undoing the initial group L.
If I just do Select L, SUM(M) where G contains 'HDW' group by L it groups everything correctly. But I need those other columns to be in this. Once I add those it starts to undo the initial group for some reason.
I tried using one of your other tutorials where you can combine multiple QUERY's into one but that didn't produce anything different.
Like it
Anyone have any ideas how to just add column in group by query that is not number?
Hi, very helpfull videos.
In more details:
I have a problem that I can not think of a solution. I have data set witch needs to be group by.
My problem is that I need to add extra columns to my group by query (extra data I use to see what is going on with product that I'm grouping and that extra columns are text), I just need to COPY data from multiple columns in every first row of each unique item of 'query group by' column.
Hope I explained it well enough.
Thx,
Cheers
can you please share a sample sheet
Hi, Is there potentially any syntax error in something like this?
=QUERY(Data_Dump!A:U, "SELECT A,B,C,D,E,F,G,H,I,J,K,L, SUM(M), N,O,P,Q,R,S,T,U GROUP BY H",1) ?
You can't list columns without an aggregate function in SELECT if they don't appear in GROUP BY.
Thank you teacher! Have a question, can this function get aggregate data using COUNTA OR COUNTUNIQUE?
No.
@@ExcelGoogleSheets that's sad
hi, i have a function to bring me a index on google finance with a historical price of VISA stock =QUERY(index(GOOGLEFINANCE("V","PRICE","01/01/2020",TODAY()),,2),"select avg(A)") , how i can use query to do a avg in this index ? i try do somethings but this dont work, google show me a message like "the column A dont exist". someone can help me ?
=QUERY(index(GOOGLEFINANCE("V","PRICE","01/01/2020",TODAY()),,2),"select avg(Col1)")
=QUERY(GOOGLEFINANCE("V","PRICE","01/01/2020",TODAY()),"select avg(Col2)",1)
why i cant group/pivot without AGG
It will be great to have a video on format with the query function (like how to query results and format as currency, etc). If you agree, up vote this comment if you agree so the author notices it.
ruclips.net/video/eQKmAcdVccs/видео.html
Hi. I have learnt a lot from your videos and have a question. Is there a way where I can use the QUERY function to get the unique count of no.of sales order for a date. Ie. SO-001 would have three items and each item is entered in different rows. SO-002 would have 1 item, SO-003 would have two items. The summary sheet should have a query function that shows the count of no. of sales orders as 3 and not 6 for that date.
Please help if possible. Thank you
QUERY language currently doesn't support DISTINCT, so you would have to pre-process data to remove duplicates first before grouping it.
@@ExcelGoogleSheets Thank you for your response. Will work on it.
Why not just use a Pivot Table with countunique?
@@ExcelGoogleSheets I did. But the summary sheet is an automated sheet. But when I give the pivot table a range (i.e A:F instead hardcoding A1:F520) it keeps showing an additional 0 values in the pivot table which Im assuming its the blank cells from the range. Is there a workaround for this?
I used a filter on the pivot table to remove the 0 but then I wanted some method which is more cleaner and leaner. If that makes sense.
HOW CAN I GET THE LAST SALE OF FELIPE WEBER?
Sort by date, filter to felipe and limit to 1
how to get sales in Millions with above formula?
How to add a total row to a Query Function table
Calculate the total separately and append it as an array.
Provide practices sheet