SQLite GROUP BY provision is utilized in a joint effort with the SELECT assertion to mastermind indistinguishable information into gatherings.
Gathering BY proviso follows the WHERE condition in a SELECT proclamation and goes before the ORDER BY statement.
Syntax
Following is the fundamental language structure of GROUP BY provision. Gathering BY statement should follow the conditions in the WHERE provision and should go before ORDER BY proviso on the off chance that one is utilized.
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
You can utilize more than one section in the GROUP BY statement. Ensure whatever section you are utilizing to gathering, that segment ought to be accessible in the segment list.
Example
Consider COMPANY table with the accompanying 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
On the off chance that you need to know the aggregate sum of pay on every client, at that point GROUP BY inquiry will be as per the following −
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
This will deliver the accompanying outcome −
NAME SUM(SALARY)
---------- -----------
Allen 15000.0
David 85000.0
James 10000.0
Kim 45000.0
Mark 65000.0
Paul 20000.0
Teddy 20000.0
Presently, let us make three additional records in COMPANY table utilizing the accompanying INSERT proclamations.
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 );
Presently, our table has the accompanying records with copy names.
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
Once more, let us utilize similar proclamation to bunch by all the records utilizing NAME segment as follows −
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
This will deliver the accompanying outcome.
NAME SUM(SALARY)
---------- -----------
Allen 15000
David 85000
James 20000
Kim 45000
Mark 65000
Paul 40000
Teddy 20000
Allow us to utilize ORDER BY condition alongside GROUP BY proviso as follows −
sqlite> SELECT NAME, SUM(SALARY)
FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
This will deliver the accompanying outcome.
NAME SUM(SALARY)
---------- -----------
Teddy 20000
Paul 40000
Mark 65000
Kim 45000
James 20000
David 85000
Allen 15000