SQLite COUNT()函式

SQLite COUNT()函式用於檢索表示式或給定列的行數。

語法

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

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

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

範例1：

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

從student表中檢索AGE大於22的學生人數：

SELECT COUNT(*) AS "Number of students"  
FROM STUDENT  
WHERE AGE > 22;

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

sqlite> SELECT COUNT(*) AS "Number of students"
   ...> FROM STUDENT
   ...> WHERE AGE > 22;
8
sqlite>

範例2：

計算AGE大於22歲的學生人數，並按學生名字分組。

SELECT NAME, COUNT(*) AS "Number Of Students"  
FROM STUDENT  
WHERE AGE > 22  
GROUP BY NAME;

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

sqlite> SELECT NAME, COUNT(*) AS "Number Of Students"
   ...> FROM STUDENT
   ...> WHERE AGE > 22
   ...> GROUP BY NAME;
Avgsu|1
Linsu|2
Maxsu|2
Minsu|2
Sqlsu|1
sqlite>

範例3：

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

SELECT ADDRESS, COUNT(*) AS "Number Of Students"  
FROM STUDENT  
GROUP BY ADDRESS;

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

sqlite> SELECT ADDRESS, COUNT(*) AS "Number Of Students"
   ...> FROM STUDENT
   ...> GROUP BY ADDRESS;
Beijing|1
Guangzhou|2
Haikou|2
Shanghai|1
Shengzhen|2
Shenzhen|1
sqlite>