SQLite Group By Clause

SQLite is a server-less database engine and it is written in C programming language. It is developed by D. Richard Hipp in the year 2000. The main moto for developing SQLite is to escape from using complex database engines like MYSQL etc. It has become one of the most popular database engines as we use it in Television, Mobile Phones, Web browsers, and many more. It is written simply so that it can be embedded into other applications.

In this article, you will learn about what you mean by SQLite Group By clause, how it works, and its functionality by using examples. Group By clause is used with the SELECT statement to arrange the different rows of data into one or more columns by grouping them.

SQLite GROUP BY Clause

SQLite Group By clause is one of the most frequently used clauses in the SQLite. It is used to group the similar data into groups. To group the data we need to apply the aggregate functions on the group of rows by defining the same values in the specified columns. For example, we have a products table and we have different products now what if we want to gather all the same products then here comes the SQLite Group by clause to group all the similar product_id values. This is the use of SQLite Group by Clause.

SQLite Group by clause is used after the Where clause, if the condition is satisfied then only the Group by clause works, and if you want to use the Order by clause then it is followed by Group by clause.

We can use multiple columns in the group by clause but you need to remember that the columns that you have mentioned must be in the table.

Syntax:

SELECT expression1, expression2, ... expression_n, aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Explanation: In the syntax you can see that we are going to select the columns and applying the aggregate functions like SUM, COUNT, MAX, MIN and followed by the table from which you are going to fetch the data, where condition followed by the Group by expression. Where condition can be specified or not it is not necessary.

Examples of SQLite Group By Clause

Here we are going to use the Company table and the table has consists of ID, Name, Age, Address and Salary as a Columns. If you don’t know How to Create Table in SQLite then refer this.

Company Table

Example 1: Group By Using SUM Function

Let’s use the SUM function along with the group by clause. We use sum function to calculate the sum, length of any column. SQLite sum function is an aggregate function.

Syntax:

SELECT expression1, expression2, ... expression_n, sum (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Query:

Let’s calculate the sum of salary of all employees where the salary of employees should be more than 4000 grouped by their names.

SELECT name, SUM(salary) as total
FROM Company
WHERE salary > 40000
group by name
GROUP BY name desc;

Output:

Group by Sum Function

Explanation: Here we are fetching the name and sum of salary form the company table and there we specified a where condition where salary must be greater than 40000 and going to group by name in the descending order. In the output, you can see that the Name column is in descending as we have specified and according to that total column is arranged.

Example 2: Group By Using COUNT Function

Let us use the Count function along with the group by clause. We use count function to count the number of rows in a particular column. SQLite count function is an aggregate function.

Syntax:

SELECT expression1, expression2, ... expression_n, count(expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Query:

Let’s count the number of employees on the basis of those who are getting the same amount of salary in particular group using GROUP BY and COUNT Function.

SELECT salary, COUNT(name) as no_of_employees
FROM Company
GROUP BY salary;

Output:

Group by Count Function

Explanation: Here we are fetching the salary and count of name as no_of_employees form the company table and and going to group by salary. In the result, we have clearly saw that the number of employees get the same amount of salary in the particular group.

Example 3: Group By Using MIN Function

Let’s use the MIN function along with the group by clause. We use minimum function to display the number of rows those have the minimum value in the particular column. SQLite minimum function is an aggregate function. It returns the lowest values of an expression

Syntax

SELECT expression1, expression2, ... expression_n, min(expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Query:

Let’s determine the minimum age of an employee along with their name from each group in the Company table.

SELECT  name, MIN(Age) as Minage
FROM Company
GROUP BY name
ORDER BY age;

Output:

Grou by Min Function

Explanation: In the above query, We have fetch the name and minimum age as a Minage column from the table.

Example 4: Group By Using Max Function

Let’s use the Max function along with the group by clause. We use maximum function to display the number of rows those have the maximum value in the particular column. SQLite maximum function is an aggregate function. It returns the highest values of an expression

Syntax:

SELECT expression1, expression2, ... expression_n, max(expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Query:

Let’s determine the maximum salary of an employee from each group in the Company table.

SELECT ID, MAX(salary) as Maxsal
FROM  Company
GROUP BY ID
ORDER BY  salary desc;

Output:

Group by Max Function

Explanation: In the above query, We have fetch the ID and maximum salary as a Maxsal column and we are going to group by ID and specified order is by salary in the descending order.

Conclusion

SQLite Group by clause is used to group the similar data into groups. It is used to perform aggregate functions like Sum, Count, Max and Min. In this article we have learnt that how does SQLite Group by clause works and we also have seen few examples for better understanding. SQLite Group by clause is to be written after the Where clause and before the Order by clause.