YouTip

# SQLite Group By The SQLite **GROUP BY** clause is used with the SELECT statement to group rows that have the same values. In a 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 follow the conditions in the WHERE clause and must precede the ORDER BY clause if present. SELECT column-list FROM table_name WHERE GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN You can use more than one column in the GROUP BY clause. Make sure that the grouping column you use is in the column list. ## Example Consider the COMPANY table with the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0 If you want to know the total salary of each customer, then you can use the GROUP BY query as follows: sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME; This will produce the following result: NAME SUM(SALARY)---------- -----------Allen 15000.0David 85000.0James 10000.0Kim 45000.0Mark 65000.0Paul 20000.0Teddy 20000.0 Now, let us create three more records in the COMPANY table using the INSERT statements as follows: 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 ); Now, our table has duplicate names as shown below: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.08 Paul 24 Houston 20000.09 James 44 Norway 5000.010 James 45 Texas 5000.0 Let us now use the GROUP BY statement to group all the records by the NAME column as follows: sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME; This will produce the following result: NAME SUM(SALARY)---------- -----------Allen 15000David 85000James 20000Kim 45000Mark 65000Paul 40000Teddy 20000 Let us now use the ORDER BY clause with the GROUP BY clause as follows: sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC; This will produce the following result: NAME SUM(SALARY)---------- -----------Teddy 20000Paul 40000Mark 65000Kim 45000James 20000David 85000Allen 15000