[et_pb_section fb_built=”1″ _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text module_id=”avg” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Analyzing large datasets to gain insights can be a daunting task. With terabytes or even petabytes of data, manually reviewing rows and columns is impossible. This is where <\/span>BigQuery<\/span><\/a> aggregate functions come in handy.<\/span><\/p>\n BigQuery is Google’s fully managed enterprise data warehouse that allows you to run fast queries on large datasets. One of the greatest benefits of BigQuery is its ability to aggregate data – combining information from multiple rows to help you gain insights.<\/span><\/p>\n With these functions, you can easily summarize and analyze large amounts of data.<\/span><\/p>\n In this article, you’ll learn everything you need to start using simple functions such as COUNT, SUM, AVG, MIN and MAX.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”aggregate” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Aggregate functions are an essential component of BigQuery. These powerful functions enable you to derive meaningful insights from large datasets by performing calculations across multiple rows of data.<\/span><\/p>\n With aggregate functions, you can combine multiple rows of data into a single result or output. Some examples include calculating totals, averages, maximums or minimums across a range of data.<\/span><\/p>\n Whether you need to sum revenue over time, find the average order value, or count the number of clients – aggregate functions have you covered. Here are some examples of these functions.<\/span><\/p>\n AVG<\/b>: returns the average of all non-null values.<\/span><\/p>\n COUNT<\/b>: returns the total number of rows.<\/span><\/p>\n COUNTIF<\/b>: returns the number of rows that meet a specified condition.<\/span><\/p>\n MAX<\/b>: returns the maximum non-null value.<\/span><\/p>\n MIN<\/b>: returns the minimum non-null value.<\/span><\/p>\n SUM<\/b>: returns the sum of all non-null values.<\/span><\/p>\n Let’s take a look at these aggregate functions and learn how to make the most of them. We’ll use this public dataset containing information about all the world population divided by country, from the year 1960 onwards.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-aggregate-functions-1.png” title_text=”BigQuery aggregate functions 1″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”AVG” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let’s start with the AVG function. This function will return the average of all non-null values. Here’s the syntax:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT AVG(column)<\/span><\/p>\n FROM table_name;<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I’ll write a query to calculate the average of the population of these 6 countries in 2000: China, Germany, Russia, Japan, India, Brazil.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT AVG(year_2000)<\/span><\/p>\n FROM `bigquery-public-data.world_bank_global_population.population_by_country`<\/span><\/p>\n WHERE country IN (‘China’, ‘Germany’, ‘Russia’, ‘Japan’, ‘India’, ‘Brazil’)<\/span><\/p>\n LIMIT 100;<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let’s break this formula down:<\/span><\/p>\n SELECT AVG(year_2000) \u2013 This statement specifies that we want to calculate the average of the specified column.\u00a0<\/span><\/p>\n FROM `bigquery-public-data.world_bank_global_population.population_by_country` \u2013 This specifies the table we are querying.<\/span><\/p>\n WHERE country IN (‘China’, ‘Germany’, ‘Russia’, ‘Japan’, ‘India’, ‘Brazil’)<\/span>\u00a0\u2013 This WHERE clause filters the rows to only those countries in parentheses.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-aggregate-functions-2.png” alt=”BigQuery aggregate functions 2 When we run the query, BigQuery returns the average of the population of these 6 countries.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”count” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now we’ll take a closer look at the COUNT function. When we run this function, BigQuery returns the total number of rows. Here’s the syntax we’ll use:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT COUNT(column)<\/span><\/p>\n FROM table_name;<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I’ll run a simple query to count the number of rows contained in the table. We’ll use the “country” column as a reference.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT COUNT(country)<\/span><\/p>\n FROM `bigquery-public-data.world_bank_global_population.population_by_country`<\/span><\/p>\n LIMIT 100;<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-aggregate-functions-3.png” alt=”BigQuery aggregate functions 3″ title_text=”BigQuery aggregate functions 3″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n After running this query, BigQuery has returned the number of rows: 264.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”countif” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n With the COUNTIF function, BigQuery will return the number of rows that meet a certain condition. Here’s the syntax:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT COUNTIF(condition)<\/span><\/p>\n FROM table_name;<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let’s say I want BigQuery to return the number of rows where the population is less than 100.000 in a specific year. I’ll write the query specifying the condition: column year_2000 is less than 100.000.<\/span><\/p>\n This is what the query would look like:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT COUNTIF(year_2000<100000)<\/span><\/p>\n FROM `bigquery-public-data.world_bank_global_population.population_by_country`<\/span><\/p>\n LIMIT 100;<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-aggregate-functions-4.png” alt=”BigQuery aggregate functions 4″ title_text=”BigQuery aggregate functions 4″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When I execute this query, BigQuery returns the number of rows that meet this condition: 27.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”max” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n By using the MAX function, I can ask BigQuery to return the maximum non-null value in a specific column. This is the syntax we’ll use.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT MAX(column)<\/span><\/p>\n FROM table_name;<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I’ll write a query that will return the maximum value contained in the year_2000 column. In addition, I’ll add a WHERE clause to restrict the query to these 6 countries: China, Germany, Russia, Japan, India, Brazil.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT MAX(year_2000)<\/span><\/p>\n FROM `bigquery-public-data.world_bank_global_population.population_by_country`<\/span><\/p>\n WHERE country IN (‘China’,’Germany’,’Russia’,’Japan’, ‘India’, ‘Brazil’)<\/span><\/p>\n LIMIT 100;<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-aggregate-functions-5.png” alt=”BigQuery aggregate functions 5″ title_text=”BigQuery aggregate functions 5″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When I execute the query, it returns the maximum value within the specified range: 1,262,645,000.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”min” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As opposed to the MAX function, the MIN function will return the minimum value in a column. This is what the syntax looks like.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT MIN(column)<\/span><\/p>\n FROM table_name;<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now, I’ll just replace the MAX function with MIN.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT MIN(year_2000)<\/span><\/p>\n FROM `bigquery-public-data.world_bank_global_population.population_by_country`<\/span><\/p>\n WHERE country IN (‘China’, ‘Germany’,’Russia’,’Japan’, ‘India’, ‘Brazil’)<\/span><\/p>\n LIMIT 100;<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-aggregate-functions-8.png” alt=”BigQuery aggregate functions 7″ title_text=”BigQuery aggregate functions 8″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n BigQuery has just returned the minimum value. The smallest population is north of 82 million.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”sum” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Lastly, we’ll take a closer look at the SUM function. By using this function, I’ll ask BigQuery to calculate the sum of all the values.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT SUM(column)<\/span><\/p>\n FROM table_name;<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here, I’ll write a query to calculate the sum of the population of the same 6 countries.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n SELECT SUM(year_2000)<\/span><\/p>\n FROM `bigquery-public-data.world_bank_global_population.population_by_country`<\/span><\/p>\n WHERE country IN (‘China’, ‘Germany’,’Russia’,’Japan’, ‘India’, ‘Brazil’)<\/span><\/p>\n LIMIT 100;<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-aggregate-functions-7.png” alt=”BigQuery aggregate functions 6″ title_text=”BigQuery aggregate functions 7″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As a result, BigQuery has calculated the population of these 6 countries: over 2.7 billion.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”use” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n There you have it! That’s how you can use aggregate functions in BigQuery. By using these functions, you can combine and summarize data from multiple rows into a single value. If you want to combine tables instead, check out this article on how to use <\/span>BigQuery joins<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" How to use BigQuery aggregate functions Analyzing large datasets to gain insights can be a daunting task. With terabytes or even petabytes of data, manually reviewing rows and columns is impossible. This is where BigQuery aggregate functions come in handy. BigQuery is Google’s fully managed enterprise data warehouse that allows you to run fast queries […]<\/p>\n","protected":false},"author":42,"featured_media":43848,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[31],"tags":[],"class_list":["post-43726","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-science"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/43726","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/42"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=43726"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/43726\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/43848"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=43726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=43726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=43726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}What are aggregate functions in BigQuery?<\/span><\/h2>\n
BigQuery aggregate functions: AVG<\/span><\/h2>\n
\n” title_text=”BigQuery aggregate functions 2″ align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\nBigQuery aggregate functions: COUNT<\/span><\/h2>\n
BigQuery aggregate functions: COUNTIF<\/span><\/h2>\n
BigQuery aggregate functions: MAX<\/span><\/h2>\n
BigQuery aggregate functions: MIN<\/span>\u00a0<\/h2>\n
BigQuery aggregate functions: SUM<\/span><\/h2>\n
How to use BigQuery aggregate functions<\/span><\/h2>\n