SQLite GROUP BY Clause

The SQLite GROUP BY clause is used with SELECT statement to collaborate the same identical elements into groups.

The GROUP BY clause is used with WHERE clause in SELECT statement and precedes the ORDER BY clause.

Syntax:

snippet
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

Let's take an example to demonstrate the GROUP BY clause. We have a table named "STUDENT", having the following data:

Sqlite Group by clause 1

Use the GROUP BY query to know the total amount of FEES of each student:

snippet
SELECT NAME, SUM(FEES) FROM STUDENT GROUP BY NAME;

Output:

Sqlite Group by clause 2

Now, create some more records in "STUDENT" table using the following INSERT statement:

snippet
INSERT INTO STUDENT VALUES (7, 'Ajeet', 27, 'Delhi', 10000.00 );
INSERT INTO STUDENT VALUES (8, 'Mark', 23, 'USA', 5000.00 );
INSERT INTO STUDENT VALUES (9, 'Mark', 23, 'USA', 9000.00 );
Sqlite Group by clause 3

The new updated table has the inserted entries. Now, use the same GROUP BY statement to group-by all the records using NAME column:

snippet
SELECT NAME, SUM(FEES) FROM STUDENT GROUP BY NAME ORDER BY NAME;

Output:

Sqlite Group by clause 4

You can use ORDER BY clause along with GROUP BY to arrange the data in ascending or descending order.

snippet
SELECT NAME, SUM(FEES) 
FROM STUDENT GROUP BY NAME ORDER BY NAME DESC;

Output:

Sqlite Group by clause 5
Related Tutorial
1 SQLite WHERE Clause
2 SQLite AND Operator
3 SQLite OR Operator
4 SQLite LIKE Clause (Operator)
5 SQLite GLOB Clause (Operator)
6 SQLite LIMIT Clause
7 SQLite ORDER BY Clause
8 SQLite GROUP BY Clause

9 SQLite HAVING Clause
10 SQLite DISTINCT Clause
11 SQLite Union Operator
12 SQLite Union All Operator
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Try it
http://codeamaze.com
Contents +