Here we will learn the SQLite group by clause with example and how to use sqlite group by having min / max , sqlite group by multiple columns, sqlite group by having clause, sqlite select group by having count with examples.
In SQLite GROUP BY clause is used to aggregate data into a single row where the value of one or more specified columns is repeated. This feature can be used to reduce the number of records to only find unique values of a column.
Following is the syntax of using GROUP BY in the SELECT statement.
SELECT result
FROM [table-list]
GROUP BY [expr-list]
If you observe above SQLite Group By syntax it contains few properties those are
In SQLite GROUP BY clause takes a list of expressions usually column names from the result.
Create a vegetable table and insert some data like as shown below.
CREATE TABLE vegetable(
name TEXT,
color TEXT);
INSERT INTO vegetable
values ('peace','green'),
('carrot','orange'),
('cucumber','green');
Following is the syntax of using SQLite GROUP BY clause.
GROUP BY grouping_expression;
Now let’s look at the example of a vegetable table, in which we had more than one green and orange vegetable so grouping on the color column will return each value only once.
sqlite> SELECT color FROM vegetable GROUP BY color;
color
----------
green
orange
The grouping process has two steps. First, the GROUP BY expression list is used to arrange table rows into different groups. Once the groups are defined, the SELECT defines how those groups are flattened down into a single row.
Here in the above example first it creates a group of different colors that are available in a vegetable table then it represents each group in a single row.
We will see how to use SQLite Group By clause with sum function for that create a table called emp_master and insert some data like as shown below.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (1,'Honey','Patel',10100,1),
(2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2);
Once we create and inserted data in emp_master table that would contain following records.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
Now, If you want to know the total amount of salary on each employee, then GROUP BY query would be like as shown below.
sqlite> SELECT first_name, SUM(salary) FROM emp_master GROUP BY first_name;
When we run above SQLite Group By query we will get a result like as shown below.
first_name SUM(salary)
---------- -----------
Honey 10100
Jagruti 9500
Shweta 19300
Vinay 35100
Now, consider that emp_master table contains two employees with the same name as follows:
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
If you observe the above result we have two users having the same first_name (shweta). Now, if we fire SQLite Group By query then the result would be different because it takes two employees who are having the same name as one group. So, we will fire the same query and examine the result that would be like as shown below.
sqlite> SELECT first_name, SUM(salary) FROM emp_master GROUP BY first_name;
first_name SUM(salary)
---------- -----------
Honey 10100
Jagruti 9500
Shweta 31300
Vinay 35100
We will see how to use SQLite Group By with count function for that just change the above query a little bit and add count(first_name) in SELECT like as shown below.
sqlite> SELECT first_name, SUM(salary), count(first_name) FROM emp_master GROUP BY first_name;
When we run above query we will get result like as shown below.
first_name SUM(salary) count(first_name)
---------- ----------- -----------------
Honey 10100 1
Jagruti 9500 1
Shweta 31300 2
Vinay 35100 1
If you observe the above result, it counts how many employees are having the same first_name in the emp_master table.
We will see how to use SQLite Group By with Having clause for that we need to write query like as shown below by using SQLite GROUP BY with HAVING clause.
SELECT first_name, COUNT(first_name) FROM emp_master
GROUP BY first_name HAVING COUNT(first_name) > 1;
If you observe the above query we written condition to get employees whose name matching more than one. We will run and check the result that will be like as shown below.
first_name COUNT(first_name)
---------- -----------------
Shweta 2
The above output contains two or more employees having the same first_name.
Now, we will get a minimum and maximum salary for all the departments by using SQLite Group By statement. For that, we need to create a dept_master table and insert some data like as shown below.
CREATE TABLE dept_master
(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_name TEXT);
INSERT INTO dept_master(dept_name)
VALUES('Admin'),
('Sales'),
('Quality Control');
('Marketing');
Once we create and insert data execute following query to see the data of dept_master table.
sqlite> SELECT * FROM dep_master;
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Co
4 Marketing
Now update emp_master table by inserting new records like as shown below.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
Now write the following query to get the minimum and maximum salary of the department by using SQLite Group By clause.
SELECT min(e.salary), max(e.salary),d.dept_name
FROM emp_master e, dept_master d
WHERE e.dept_id = d.dept_id group by e.dept_id;
When we execute above query we will get result like as shown below.
min(e.salary) max(e.salary) dept_name
------------- ------------- ------------
10100 13000 Admin
9500 19300 Sales
12000 50000 Quality Co
We can also use the GROUP BY clause with multiple columns. Let’s look at the simple example of using SQLite Group By with multiple columns.
sqlite> SELECT * FROM emp_master GROUP BY dept_id, first_name;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
6 Sonal Menpara 13000 1
4 Jagruti Viras 9500 2
2 Shweta Jariwala 19300 2
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
5 Shweta Rana 12000 3
3 Vinay Jariwala 35100 3
This is how we can use SQLite Group By clause to perform multiple operations based on our requirements.