SQLite GROUP BY Keyword

The SQLite GROUP BY keyword is used to arrange result table into identical groups with the help of aggregate functions (COUNT, MAX, MIN, SUM, AVG etc). The GROUP BY keyword follows the WHERE keyword in a SELECT statement and precedes the ORDER BY keyword.

Syntax

The syntax for using GROUP BY Keyword in SQLite is given below:

SELECT column1, column2
FROM table_name
WHERE condition(s)
GROUP BY column1, column2
ORDER BY column1, column2;

Example:

Consider a database table called Employee with the following records:

• GROUP BY single column: To find the number of employee present in the Employee table in different age group sorted by Age (descending order), the query is:

  SELECT Age, Count(Name) AS Number_of_Employee
  FROM Employee
  GROUP BY Age
  ORDER BY Age DESC;
  

  
  

  This will produce the result as shown below:

  Age	Number_of_Employee
  30	1
  28	2
  27	1
  25	1
  24	1

• GROUP BY multiple columns: To find the average salary of employees present in the Employee table ordered by City and Age, the query is:

  SELECT City, Age, AVG(Salary) AS AverageSalary
  FROM Employee
  GROUP BY City, Age
  ORDER BY City, Age;
  

  
  

  This will produce the result as shown below:

  City	Age	AverageSalary
  Amsterdam	30	3100.0
  London	25	3000.0
  London	28	2900.0
  New York	24	2750.0
  Paris	27	2800.0