# SQL聚合函式

SQL聚合函式用於對錶的單個列的多行執行計算，它只返回一個值。它還用於匯總資料。

SQL聚合函式的型別，如下圖所示 -

## 1.COUNT函式

• `COUNT`函式用於計算資料庫表中的行數，它可以在數位和非數位資料型別上工作。
• `COUNT`函式使用`COUNT(*)`返回指定表中所有行的計數。 `COUNT(*)`包函重複值和`NULL`值。

``````COUNT(*)
# 或者
COUNT( [ALL|DISTINCT] expression )
``````

PRODUCT COMPANY QTY RATE COST
Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Item6 Cpm1 3 25 75
Item7 Com1 5 30 150
Item8 Com1 3 10 30
Item9 Com2 2 25 50
Item10 Com3 4 30 120

``````SELECT COUNT(*)
FROM PRODUCT_MAST;
``````

``````10
``````

``````SELECT COUNT(*)
FROM PRODUCT_MAST;
WHERE RATE>=20;
``````

``````7
``````

``````SELECT COUNT(DISTINCT COMPANY)
FROM PRODUCT_MAST;
``````

``````3
``````

``````SELECT COUNT(DISTINCT COMPANY)
FROM PRODUCT_MAST;
``````

``````3
``````

``````SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY;
``````

``````Com1    5
Com2    3
Com3    2
``````

``````SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING COUNT(*)>2;
``````

``````Com1    5
Com2    3
``````

## 2. SUM函式

`Sum`函式用於計算所有選定列的總和。它僅適用於數位型別的欄位。

``````SUM()
# 或
SUM( [ALL|DISTINCT] expression )
``````

``````SELECT SUM(COST)
FROM PRODUCT_MAST;
``````

``````670
``````

``````SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3;
``````

``````320
``````

``````SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3
GROUP BY COMPANY;
``````

``````Com1    150
Com2    170
``````

``````SELECT COMPANY, SUM(COST)
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING SUM(COST)>=170;
``````

``````Com1    335
Com3    170
``````

## 3. AVG函式

AVG函式用於計算數值型別的平均值。`AVG`函式返回所有非`Null`值的平均值。

``````AVG()
#或
AVG( [ALL|DISTINCT] expression )
``````

``````SELECT AVG(COST)
FROM PRODUCT_MAST;
``````

``````67.00
``````

## 4. MAX函式

MAX函式用於查詢某列的最大值，此函式確定列的所有選定值的最大值。

``````MAX()
# 或
MAX( [ALL|DISTINCT] expression )
``````

``````SELECT MAX(RATE)
FROM PRODUCT_MAST;
``````

``````30
``````

## 5. MIN函式

MIN函式用於查詢某列的最小值，此函式確定列的所有選定值的最小值。

``````MIN()
# 或
MIN( [ALL|DISTINCT] expression )
``````

``````SELECT MIN(RATE)
FROM PRODUCT_MAST;
``````

``````10
``````