# Let’s Use Spreadsheet Query Function (5) Group by

この記事は約7分で読めます。

• who are interested in SQL, but who are troublesome to prepare the environment and data from scratch
• who are extracting data with vlookup, but who feel the limit
• who want to analyze data
• Even non-programmers who want to use queries
• who are asking queries delicious? (meaning that the query is delicious)

## Query function series planned (planned)

1. Outline and purpose
2. Syntax of query function
3. Basic SELECT clause (get all, get only specific columns)
4. Extraction basics Try using WHERE clause (numerical value match, size, date match, size etc.)
5. Try using the GROUP BY clause ← this time
6. Let’s bring data from another sheet ~ Combination with ImportRange function ~
7. Sorting basics ORDER BY clause
8. Required if you do SQL Try a subquery
9. There are line breaks in the data. contains/starts with helps when you can’t handle like

## Basic of GROUP BY

Well, the last time was a fairly heavy WHERE, but this time it’s quite easy to do GROUP BY. Let’s go quickly.

### What we can by GROUP BY

As the name implies, it allows you to group column values for processing. In essence, instead of retrieving the information here, it is possible to use the number, aggregate, average, etc. of each column as a unit.

For those grouped, we will use the following aggregation process.

#### Actual usage ~ Grouping by one column ~

Let’s actually use GROUP BY.

This time, let’s calculate the salary amount by dept. The meaning of this in the query is as follows.

`=query(sample!A1:H7,"select B ,sum(D) group by B")`

Column B：dept
Column D：salary

The target of group by (column after group by) is the target of basic select. Then, if you add the data you want to aggregate with sum (column), it will sum up the B column standard (for each element of the B column) and sum the values of salary (column D).

You can see that salary is aggregated in dept units. The salary part is labeled as sum. You can see that the salary part is summed. Of course, if you set it to avg, it will be displayed as avg. The same applies to max/min.

###### Reference) Change sum salary to total salary (How to use label)

It is easy to understand even with sum salary, but it is possible to change the part of this notation. That is the element called label.

`=query(sample!A1:H7,"select B, sum(D) group by B label sum(D) '合計', B '部署'")`

The label is always added at the end of the second argument of the query function. In the case above, sum(D) (sum salary display) is changed to “total” and B (portion displayed as dept) is changed to “department”.

This change target will specify the element selected by select.

`Generic image:　＝query(data, "select A, B group by B label A 'えー', B 'びー'"`

If there are multiple, you can set multiple by separating them with a, (comma).

#### Actual usage ~ Grouping with 2 columns ~

This time, it’s a meaningless grouping, but let’s group by B and C, and let’s give the maximum value of hireDate and the minimum value of age in the group group grouped by B and C.

`=query(sample!A1:H7,"select B, C, max(E), min(F) group by B ,C")`

If you want to group multiple groups, you can put multiple columns that you want to aggregate with commas after group by.

## Summary

There are many uses of group by, such as aggregation and acquisition of maximum and minimum values. Many features are actually similar to countif/countifs and sumif/sumifs.

However, the big difference between the above function and the query function is the need to change the function when data is updated and whether it is easy to combine with other command groups such as order_by/where/label.

I want you to get used to it.

#### Series： Let’s Use Spreadsheet Query Function

While not studying SQL, I will become accustomed to using the spreadsheet query function. We will study from the simple usage of select statement, including the difference from SQL.

シェアする
ks_Accountingをフォローする programing on biz management