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:

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:

After the creation, we will insert data as:

To display the table, we use the command as:

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:

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:

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:

Using the aggregate COUNT() function

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

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:

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:

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:

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:

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.