SQLite SUM()函式

SQLite SUM()函式用於返回表示式或給定數位列的總和。

語法

SELECT SUM(aggregate_expression)  
FROM tables  
[WHERE conditions];

在SUM()函式中使用GROUP BY子句時的語法：

SELECT expression1, expression2, ... expression_n  
SUM(aggregate_expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;

範例1：

假設有一個名為student的表，具有以下資料：

從student表中檢索AGE小於24的學生總費用：

SELECT SUM(FEES) AS "Total Fees"  
FROM STUDENT  
WHERE AGE < 24;

執行上面查詢程式碼，得到以下結果 -

sqlite> SELECT SUM(FEES) AS "Total Fees"
   ...> FROM STUDENT
   ...> WHERE AGE < 24;
34000.0
sqlite>

範例2：

使用具有數學公式的SUM()函式，求每個月的學生總費用 -

SELECT SUM(FEES / 12) AS "Total Monthly Fees"  
FROM STUDENT;

執行上面程式碼，得到以下結果 -

sqlite> SELECT SUM(FEES / 12) AS "Total Monthly Fees"
   ...> FROM STUDENT;
14083.3333333333
sqlite>

範例3：

計算每個地址的學生總人數，按學生地址(Address)分組。

從student表中檢索地址，並按地址分組並查詢相應費用的總和。

SELECT ADDRESS, SUM(FEES) AS "Total Salary"  
FROM STUDENT  
WHERE ID > 0
GROUP BY ADDRESS;

執行上面程式碼，得到以下結果 -

sqlite> SELECT ADDRESS, SUM(FEES) AS "Total Salary"
   ...> FROM STUDENT
   ...> WHERE ID > 0
   ...> GROUP BY ADDRESS;
Beijing|15000.0
Guangzhou|70000.0
Haikou|35000.0
Shanghai|2000.0
Shengzhen|38000.0
Shenzhen|9000.0
sqlite>