sqlite
Database; use; embedded relational database
group
UK[gru:p] US [ɡrup]
n. Group, group; group, batch; (sculpture, etc.) group portrait; (British and American) Air Force Brigade
vt.& vi. To form a group, to gather
vt. Classification, classification
by
英[ba?] 美[ba?]
prep.Beside...; means Method; because; pass
adv. pass; used when expressing retention or preservation; short visit
SQLite Group By function syntax
Function:SQLite's GROUP BY clause is used with the SELECT statement to group the same data.
In the SELECT statement, the GROUP BY clause is placed after the WHERE clause and before the ORDER BY clause.
Syntax: The basic syntax of the GROUP BY clause is given below. The GROUP BY clause must be placed after the condition in the WHERE clause and before the ORDER BY clause.
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
You can use multiple columns in the GROUP BY clause. Make sure the grouping you are using is listed in the column list.
SQLite Group By function example
COMPANY 表有以下記錄: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 如果您想了解每個客戶的工資總額,則可使用 GROUP BY 查詢,如下所示: sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME; 這將產(chǎn)生以下結(jié)果: NAME SUM(SALARY) ---------- ----------- Allen 15000.0 David 85000.0 James 10000.0 Kim 45000.0 Mark 65000.0 Paul 20000.0 Teddy 20000.0 現(xiàn)在,讓我們使用下面的 INSERT 語句在 COMPANY 表中另外創(chuàng)建三個記錄: INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 ); INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 ); INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 ); 現(xiàn)在,我們的表具有重復(fù)名稱的記錄,如下所示: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0 讓我們用同樣的 GROUP BY 語句來對所有記錄按 NAME 列進(jìn)行分組,如下所示: sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME; 這將產(chǎn)生以下結(jié)果: NAME SUM(SALARY) ---------- ----------- Allen 15000 David 85000 James 20000 Kim 45000 Mark 65000 Paul 40000 Teddy 20000 讓我們把 ORDER BY 子句與 GROUP BY 子句一起使用,如下所示: sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC; 這將產(chǎn)生以下結(jié)果: NAME SUM(SALARY) ---------- ----------- Teddy 20000 Paul 40000 Mark 65000 Kim 45000 James 20000 David 85000 Allen 15000