SQLBolt：https://sqlbolt.com/lesson/introduction

SQL Lesson 10: Queries with aggregates (Pt. 1)

使用語法 & 概念

• aggregation function （聚合函數）

  In addition to the simple expressions that we introduced last lesson, SQL also supports the use of aggregate expressions (or functions) that allow you to summarize information about a group of rows of data. With the Pixar database that you've been using, aggregate functions can be used to answer questions like, "How many movies has Pixar produced?", or "What is the highest grossing Pixar film each year?".

• 語法結構

  -- Select query with aggregate functions over all rows
  SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
  FROM mytable
  WHERE constraint_expression;
  

• Grouped aggregate functions：GROUP BY

  In addition to aggregating across all the rows, you can instead apply the aggregate functions to individual groups of data within that group (ie. box office sales for Comedies vs Action movies).
  This would then create as many results as there are unique groups defined as by the GROUP BY clause.

  GROUP BY 敘述句搭配聚合函數 (aggregation function) 使用，是用來將查詢結果中特定欄位值相同的資料分為若干個群組，而每一個群組都會傳回一個資料列。若沒有使用 GROUP BY，聚合函數針對一個 SELECT 查詢，只會返回一個彙總值。

  引用自：GROUP BY 敘述句 (SQL GROUP BY Statement)

• 語法結構

  -- Select query with aggregate functions over groups
  SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
  FROM mytable
  WHERE constraint_expression
  GROUP BY column;
  

SQL Lesson 10: Queries with aggregates (Pt. 1)

在 SQL 語句中，我們可以使用聚合函數，針對選取的欄位值進行一些計算（返回單一值）。

以下先來解題：

題目 1

-- 1. Find the longest time that an employee has been at the studi
SELECT MAX(years_employed) AS Max_years_employed
FROM employees;

題目 2

-- 2. For each role, find the average number of years employed by employees in that role
SELECT role, AVG(years_employed) AS Average_years_employed
FROM employees
GROUP BY role;

題目 3

-- 3. Find the total number of employee years worked in each building
SELECT building, SUM(years_employed) AS Total_years_employed
FROM employees
GROUP BY building;

題解

常見的聚合函數：

參考：9.20. Aggregate Functions（PostgreSQL）

參考資料：

• SQL Lesson 10: Queries with aggregates (Pt. 1)
• 9.20. Aggregate Functions（PostgreSQL）
• 函數 SQL Functions（fooish 程式技術）
• GROUP BY 敘述句｜SQL GROUP BY Statement（fooish 程式技術）