Table of Contents
- PURPOSE OF GOOGLE QUERY LANGUAGE CLAUSE- GROUP BY
- EXAMPLE DATA:
- EXAMPLE 1 : FIND THE AVERAGE MARKS OF ALL THE SUBJECTS
- EXAMPLE 2: FIND OUT THE NUMBER OF STUDENTS OPTING FOR A PARTICULAR SUBJECT AND FIND THE AVERAGE OF EACH SUBJECT.
- ISSUES WHILE USING GROUP BY STATEMENT
GOOGLE SHEETS is a great and free for personal use SPREAD SHEET software which is getting a good attention these days.
There are many useful functions which are found in almost all the spreadsheet apps like Google Sheets or Microsoft Excel or Open Calc and so on but every application has a certain special portion which is unique from the others.
One of such features in GOOGLE SHEETS is the use of the QUERY FUNCTION which enables us to use the SQL SERVER type database queries on our data.
This QUERY LANGUAGE IS KNOWN AS THE GOOGLE VISUALIZATION API QUERY LANGUAGE and is almost similar to the SQL query language.
There are many standard clauses which are required to be understood so that we can make use of this powerful function easily.
In this article , we are going to focus on the GOOGLE SHEETS QUERY LANGUAGE clause GROUP BY and learn its use.
PURPOSE OF GOOGLE QUERY LANGUAGE CLAUSE- GROUP BY
Every clause in Google Sheets query language or Google query language or Google visualization api query language has specific task to do.
When we have a big data and if we want to perform some aggregating type of operation on the data, the result will become compact and we’d need some function to wrap it up.
This particular situation is handled by the GROUP BY clause very easily.
THE GROUP BY QUERY GROUPS THE RESULT INTO THE SEPARATE ROWS AFTER AGGREGATING THE VALUES. THE RESULT IS SORTED AS PER THE GROUP BY COLUMNS AUTOMATICALLY IF NOT EXPLICITLY CHANGED BY THE ORDER BY CLAUSE.
The definition simply means that we use GROUP BY while aggregating the result.
We have the marks of different subjects and we want to check out the average marks of different subjects, we can do so easily with the help of GROUP BY statement. [ This will be taken as an example in the later section ].
We’ll try to understand the usage with the help of a few examples.
Let us take a random data and try out our newly learned clause – GROUP BY.
EXAMPLE 1 : FIND THE AVERAGE MARKS OF ALL THE SUBJECTS
Let us try to find out the average marks of all the subjects [ Not students as we are discussing the marks of a single subject ] for the given data.
Follow the steps to find out the Subject wise average marks:
- Select the cell where you want to get the result by double clicking it.
- Enter the function as =QUERY(B6:D18,”SELECT C,AVG(D) GROUP BY C”,-1) .
QUERY EXPLANATION: The QUERY used is SELECT C,AVG(D) GROUP BY C. SELECT will make the chosen columns to appear in the result. We chose two columns. C and AVG(D) , it means Subject and AVG of column D i.e. Marks will appear in the result. Group by C will group the result on the basis of fields present in the Column C.
- Press Enter.
- The result will appear as shown in the picture below.
We can see that with the help of a single statement , we could find the average of all the subjects for the given students.
Extending the same query, let us try to get the NUMBER OF STUDENTS WHO OPTED THE RESPECTIVE SUBJECT.
EXAMPLE 2: FIND OUT THE NUMBER OF STUDENTS OPTING FOR A PARTICULAR SUBJECT AND FIND THE AVERAGE OF EACH SUBJECT.
We can get this by simply getting the count of the subject.
Let us modify the query.
The query will become
=QUERY(B6:D18,”SELECT C,AVG(D),COUNT(C) GROUP BY C”,-1)
The result will appear as shown in the picture below.
so we saw the way we can make use of GROUP BY statement to get the aggregate values like SUM, AVERAGE, MAX, MIN etc. kind of values for the different groups in the given data.
ISSUES WHILE USING GROUP BY STATEMENT
The user might find it difficult to use GROUP BY statement.
But after practicing a bit, the confidence will emerge while using this clause.
Some problems might arise while using GROUP BY statement.
Therefore, remember a few points while using GROUP BY STATEMENT.
Note: If you use a
group by clause, then every column listed in the
select clause must either be listed in the
group by clause, or be wrapped by an aggregation function.
Whenever you use GROUP BY CLAUSE, All the columns listed in the Selected clause , must be included in the GROUP BY CLAUSE or BE USED WITH AN AGGREGATION FUNCTION e.g. min, max, sum, average, count.