### GROUP BY

SELECT [回傳的欄位]
FROM [資料庫.表格]
GROUP BY [進行分組的欄位名稱]
HAVING [分組後的篩選條件]
ORDER BY [排序的欄位名稱]

* WHERE是分組前設定的篩選條件
---
* GROUP BY和SELECT DISTINCT都是SQL中用於處理重複數據的語句

- SELECT DISTINCT用於選擇唯一的行。
- GROUP BY用於對行進行分組，並對每個分組進行聚合運算。

這兩者雖然都可以用來處理重複的數據，但SELECT DISTINCT更偏向於選擇唯一的數據行，而GROUP BY更適用於需要對數據進行分組並計算每個分組的情況。

---

### GROUP BY搭配聚合函數(SUM, COUNT)
#### COUNT
以5為例，在4/03出現兩次

先分組，然後計算

#### SUM

* 要確認quantity的單位為何

#### COUNT DISTINCT
* 查出顧客在每個市集日期購買多少不同種的產品

COUNT跟SUM可以同時寫入來進行查詢

### 聚合函數中放入算式

SELECT
  market_date,
  customer_id,
  vendor_id,
  quantity * cost_to_customer_per_qty AS price
FROM farmers_market.customer_purchases
WHERE
  customer_id = 3
ORDER BY market_date, vendor_id

#### GROUP BY與聚合函數做分組運算

SELECT
  customer_id,
  SUM(quantity * cost_to_customer_per_qty) AS total_spent
FROM farmers_market.customer_purchases
GROUP BY market_date

#### 連結多個表格分組與聚合
* 先將表格連結，然後才做分組聚合 - 先看到最小粒度
1.

2.

### MAX & MIN
可以搭配GROUP BY來查詢各產品類別中的最高或最低

### AVG

SELECT
  vendor_id,
  COUNT(DISTINCT product_id) AS different_products_offered,
  AVG(original_price) AS average_product_price
FROM farmers_market.vendor_inventory
WHERE market_date BETWEEN '2019-04-03' AND '2019-06-30'
GROUP BY vendor_id
ORDER BY vendor_id

- 計算方法有誤：單價/總數

#### HAVING
- 資料分組後篩選資料，而且可以使用聚合函數
（HAVING的執行順序在GROUP BY之後，SELECT之前）

SELECT
  vendor_id,
  COUNT(DISTINCT product_id) AS different_products_offered,
  SUM(quantity * original_price) AS value_of_inventory,
  SUM(quantity) AS inventory_item_count,
  SUM(quantity * original_price) / SUM(quantity) AS average_item_price
FROM farmers_market.vendor_inventory
WHERE market_date BETWEEN '2019-04-03' AND '2019-06-30'
GROUP BY vendor_id
HAVING inventory_item_count >= 100
ORDER BY vendor_id

#### COUNT(*) > 1

SELECT customer_name, COUNT(*) AS orders_count
FROM orders
GROUP BY customer_name
HAVING COUNT(*) > 1;


### 聚合函數中使用CASE語法

SUM(CASE...WHEN...THEN...ELSE...END) AS 欄位別名

因為使用聚合函數，所以需要GROUP BY