SQLite: GROUP BY Clause
This SQLite tutorial explains how to use the SQLite GROUP BY clause with syntax and examples.
Description
The SQLite GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
Syntax
The syntax for the GROUP BY clause in SQLite is:
SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
Parameters or Arguments
- expression1, expression2, ... expression_n
- The expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.
- aggregate_function
- A function such as sum, count, min, max, or avg functions.
- tables
- The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
- WHERE conditions
- Optional. The conditions that must be met for the records to be selected.
Example - Using sum function
Let's look at an example that shows how to use the GROUP BY clause with the sum function in SQLite.
This SQLite GROUP BY example uses the sum function to return the last_name and the total salaries (for the last_name).
SELECT last_name, sum(salary) AS "Total salaries" FROM employees GROUP BY last_name;
Because you have listed one column (the last_name field) in your SELECT statement that is not encapsulated in the sum function, you must use the GROUP BY clause. The last_name field must, therefore, be listed in the GROUP BY clause.
Example - Using count function
Let's look at an example that shows how to use the GROUP BY clause with the count function in SQLite.
This GROUP BY example uses the count function to return the favorite_website and the number of employees (for that favorite_website) that have an employee_id less than 50.
SELECT favorite_website, count(*) AS "Number of employees" FROM employees WHERE employee_id < 50 GROUP BY favorite_website;
Example - Using MIN function
Let's next look at how we could use the GROUP BY clause with the min function in SQLite.
This GROUP BY example uses the min function to return the name of each department and the minimum salary in the department.
SELECT department, min(salary) AS "Lowest salary" FROM employees GROUP BY department;
Example - Using max function
Finally, let's look at how we could use the GROUP BY clause with the max function in SQLite.
This GROUP BY example uses the max function to return the name of each department and the maximum salary in the department.
SELECT department, max(salary) AS "Highest salary" FROM employees GROUP BY department;
Advertisements