SQLite

Aggregate Functions in SQLite

There are many database management systems that are being used to manage the data of a website or an application like MySQL and SQLite. SQLite, like other RDMS, contains many built-in functions which make managing databases easier, like aggregate functions. In this article, we will learn about the aggregate functions in SQLite by implementing those functions in some examples.

What are the aggregate functions

Aggregate functions, perform some specific task on multiple rows, and return a single value as a result of the particular task. The aggregate functions that are used in SQLite are given in the table with the description of their usage:

Aggregate function Description
MIN() It is used to extract the maximum value inserted  in the specified column
MAX() It is used to extract the minimum value inserted in the specified column
AVG() It is used to extract the average value of all the values inserted in the specified column
COUNT() It is used to count the total entries of rows of the specified column
SUM() It is used to find out the sum of all the values of the specified column
UPPER() It is used to convert all the string values to the upper case of the specified column
LOWER() It is used to convert all the string values to the lower case of the specified column
LENGTH() It is used to find the number of characters or letters the in a specified string
ABS() It will return the absolute values of the specified column

Creating a table in SQLite

To understand the use of  all the functions of aggregate in SQLite, we will create a table using the command:

CREATE TABLE budget (id INTEGER, month TEXT, conveyance REAL, utility_bills  REAL, rent REAL, savings REAL );


After the creation, we will insert data as:

INSERT INTO budget VALUES (1,’January’,3500,3200,40000,2000),(2,’February’,4535,6700,40000,-3000),(3,’March’,1089,13465,40000,-2000),(4,’April’,9500,8132,40000,-1000);


To display the table, we use the command as:

SELECT * FROM budget;

Using the aggregate MIN() function

The minimum function will be used to find out the minimum value inserted into the columns, let us consider the table, budget, from which we want to find out the minimum value of the column utility_bills, use the command:

SELECT MIN(utility_bills) FROM budget; 

Using the aggregate MAX() function

The maximum function will be used to find out the minimum value inserted into the columns, we can find out the maximum value from the utility_bills column of the tabled budget using:

SELECT MAX(utility_bills) FROM budget; 

Using the aggregate AVG() function

If we have to find out the average of all the values inserted in a column we will use the average function like we can find out the average value of the column, conveyance, using the function AVG() as:

SELECT AVG(conveyance) FROM budget; 

Using the aggregate COUNT() function

We can find out the total number of rows of the column, id, using the function COUNT() as:

SELECT COUNT(id) FROM budget; 

Using the aggregate SUM() function

We can find out the result of adding all the values of column, rent, by using the function SUM() as:

SELECT SUM(rent) FROM budget; 

Using the aggregate UPPER() function

We can convert the strings of the column, month, into the upper case using the function UPPER() as:

Using the aggregate LOWER() function

Similarly, we can convert the strings of the column, month, into the lower case using the function LOWER() as:

SELECT LOWER(month) FROM budget; 

Using the aggregate LENGTH() function

We can calculate the number of letters or characters used in the string of the column, month,  using the function LENGTH() as:

Using the aggregate SUM() function

To add all the values of the column and find out its result, for this purpose we can use sum aggregate function le us consider the column, savings of the table, budget using the sum function:

SELECT SUM(savings) FROM budget; 

Using the aggregate ABS() function

Sometimes we have the results with negative signs and we wanted the result in absolute form, then we use the aggregate ABS() now, to find out the absolute value of the sum of a column of savings, we will use ABS() function:

SELECT ABS(SUM(savings)) FROM budget;

Conclusion

Aggregate functions can apply to the rows of a database table and can return the specified result, moreover, these functions are used to perform calculations on database entries. In this article, we discussed different aggregate functions which are commonly used in SQLite for different purposes like to find out the maximum value of the column, the minimum value of a column, and to count the number of characters of the string. We have also explained examples of each of the aggregate functions in SQLite for better understanding.

About the author

Hammad Zahid

I'm an Engineering graduate and my passion for IT has brought me to Linux. Now here I'm learning and sharing my knowledge with the world.