Monday, October 7, 2019

SQLite Group By Clause

SQLite GROUP BY Clause

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.

SQLite Group By Syntax

Following is the syntax of using GROUP BY in 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

result - Its no. of column or expression that you want as result.

table-list - Its list of table from which you want result.

expr-list - It causes one or more rows of the result to be combined into a single row of output. This is especially useful when the result contains aggregate functions.

SQLite Group By Clause Example

In SQLite GROUP BY clause takes a list of expressions usually column names from the result.

Create 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 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.

sqliteSELECT 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 above example first it creates a group of different colors that are available in vegetable table then it represents each group in single row.

SQLite Group By with Sum

We will see how to use SQLite Group By clause with sum function for that create 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.

sqliteSELECT 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.

sqliteSELECT first_nameSUM(salaryFROM emp_master GROUP BY first_name;
When we run above SQLite Group By query we will get 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 same name as follows:

sqliteSELECT 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 above result we have two users having same first_name (shweta). Now, if we fire SQLite Group By query then the result would be different because it takes two employees who have same name as a one group. So, we will fire the same query and examine the result that would be like as shown below.

sqliteSELECT first_nameSUM(salaryFROM emp_master GROUP BY first_name;

first_name  SUM(salary)
----------  -----------
Honey       10100
Jagruti     9500
Shweta      31300
Vinay       35100

SQLite Group By with Count

We will see how to use SQLite Group By with count function for that just change the above query little bit and add count(first_name) in SELECT like as shown below.

sqliteSELECT first_nameSUM(salary), count(first_nameFROM 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 above result it count how many number of employees is having same first_name in emp_master table.

SQLite Group By with Having Clause

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 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
Above output contains two or more employees having the same first_name.

SQLite Group By with Min / Max

Now, we will get minimum and maximum salary for all the departments by using SQLite Group By statement. For that we need to create 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 following query to get minimum and maximum salary of 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

SQLite Group By with Multiple Columns

We can also use 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.

No comments:

Post a Comment

Lab 09: Publish and subscribe to Event Grid events

  Microsoft Azure user interface Given the dynamic nature of Microsoft cloud tools, you might experience Azure UI changes that occur after t...