CodesJava

Easy learning with example program codes

GROUP BY Clause in SQLite

SQLite GROUP BY Clause
To group similar elements, from a table after fetching the records the GROUP BY clause is used in the SELECT statement. It is used with the WHERE clause before the ORDER BY clause.

Syntax:

SELECT column-list  
FROM table_name  
WHERE conditions 
GROUP BY column1, column2...column_N  
ORDER BY column1, column2...column_N [ASC | DESC];

Example 1:
TEACHERS Table:

ID	NAME	SALARY	SUBJECT
1	Jim	10000	Geology
2	John	20000	Geology
3	Watson	15000	Physics
4	Holmes	25000	Chemistry
5	Tony	30000	Physics
SELECT SUBJECT, SUM(SALARY) AS “SUM SALARY”
FROM TEACHERS 
GROUP BY SUBJECT;

Output:

SUBJECT	SUM SALARY
Geology	30000
Physics	45000
Chemistry	25000

Explanation:
In the above example, the SUM of the salary is grouped by the SUBJECT Column.

Example 2:
TEACHERS Table:

ID	NAME	SALARY	SUBJECT
1	Jim	10000	Geology
2	John	20000	Geology
3	Watson	15000	Physics
4	Holmes	25000	Chemistry
5	Tony	30000	Physics
SELECT SUBJECT, SUM(SALARY) 
FROM TEACHERS 
GROUP BY SUBJECT
ORDER BY SUBJECT ASC;

Output:

SUBJECT	SUM SALARY
Chemistry	25000
Geology	30000
Physics	45000

Explanation:
In the above example, the SUM of the salary is grouped by the SUBJECT Column and is than ordered in Ascending order by the SUBJECT Column.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status