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

クエリイメージ Spreadsheet
この記事は約7分で読めます。
Advertisements

Target Readers

  • 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.

processoutlinedata type of columnreturn data type
avg()Calculate the average value of numerical datanumbernumber
count()Calculate the number of dataany typenumber
max()Get maximum data
Date type: Older date is larger (oldest date data)
Character type: alphabetical order
any typesame as column data type
min()Get minimum data
Date type: Older date is larger (latest date data)
Character type: alphabetical order
any typesame as column data type
sum()Calculate the total valuenumbernumber

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.

  1. Let’s Use Spreadsheet Query Function (1) Outline and Purpose
  2. Let’s Use Spreadsheet Query Function (2) Structure of query function
  3. Let’s Use Spreadsheet Query Function (3) SELECT Clause
  4. Let’s Use Spreadsheet Query Function (4) WHERE Clause
  5. Let’s Use Spreadsheet Query Function (5) Group by
  6. Let’s Use Spreadsheet Query Function (6) Let’s bring data from another sheet ~ Combination with ImportRange function ~
  7. Let’s Use Spreadsheet Query Function (7) Sorting basics “ORDER BY” clause
  8. Let’s Use Spreadsheet Query Function (8)Required if you do SQL!! Try a subquery with the Query function
  9. Let’s Use Spreadsheet Query Function (9)line breaks in the data etc. Query function error countermeasures

コメント