Latest web development tutorials

# SQLite Group By

SQLiteGROUP BY clause for use with a SELECT statement to group the same data.

In the SELECT statement, GROUP BY clause in the WHERE clause after, placed before the ORDER BY clause.

## grammar

The following shows the basic syntax GROUP BY clause. GROUP BY clause must be placed after the WHERE clause conditions, it must be placed 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 you use the grouping columns in the column list.

## Examples

Suppose COMPANY table has the following records:

```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
```

If you want to know the total wage bill of each customer, you can use the GROUP BY query as follows:

```sqlite> SELECT NAME, SUM (SALARY) FROM COMPANY GROUP BY NAME;
```

This produces the following results:

```NAME SUM (SALARY)
---------- -----------
Allen 15000.0
David 85000.0
James 10000.0
Kim 45000.0
Mark 65000.0
Paul 20000.0
Teddy 20000.0
```

Now, let's use the following INSERT statement to create another three records in the COMPANY table:

```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 record name, as follows:

```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
```

Let us use the same GROUP BY statement to group all records Press NAME columns as follows:

```sqlite> SELECT NAME, SUM (SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
```

This produces the following results:

```NAME SUM (SALARY)
---------- -----------
Allen 15000
David 85000
James 20000
Kim 45000
Mark 65000
Paul 40000
Teddy 20000
```

Let ORDER BY clause is used in conjunction with the GROUP BY clause, as follows:

```sqlite> SELECT NAME, SUM (SALARY)
FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
```

This produces the following results:

```NAME SUM (SALARY)
---------- -----------
Teddy 20000
Paul 40000
Mark 65000
Kim 45000
James 20000
David 85000
Allen 15000
```