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

• SQL Lesson 11: Queries with aggregates (Pt. 2)

使用語法 & 概念

• HAVING

  The HAVING clause constraints are written the same way as the WHERE clause constraints, and are applied to the grouped rows. With our examples, this might not seem like a particularly useful construct, but if you imagine data with millions of rows with different properties, being able to apply additional constraints is often necessary to quickly make sense of the data.

  /* Select query with HAVING constraint */
  SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
  FROM mytable
  WHERE condition
  GROUP BY column
  HAVING group_condition;
  

SQL Lesson 11: Queries with aggregates (Pt. 2)

如果在 WHERE 語句後執行 GROUP BY ，我們可以使用 HAVING 進行條件的設定。

題目 1

-- 1. Find the number of Artists in the studio (without a HAVING clause)
SELECT role, COUNT(*) as number_of_artists
FROM employees
WHERE role = "Artist";

題目 2

-- 2. Find the number of Employees of each role in the studio
SELECT role, COUNT(*)
FROM employees
GROUP BY role;

題目 3

-- 3. Find the total number of years employed by all Engineers 
SELECT role, SUM(years_employed)
FROM employees
GROUP BY role
HAVING role = "Engineer";

--  如果不使用 GROUP BY, HAVING
SELECT role, SUM(years_employed)
FROM employees
WHERE role = "Engineer";

If you aren't using the GROUP BY clause, a simple WHERE clause will suffice.

到目前為止，SQL 的相關語法都使用到啦，明天我們將來介紹 SQL 語句的執行順序，這部分對了解 SQL 非常有幫助喔！

參考資料：

• SQL Lesson 10: Queries with aggregates (Pt. 1)
• 9.20. Aggregate Functions（PostgreSQL）
• 函數 SQL Functions （fooish 程式技術）