In this article, we are going to see how to use the aggregate function in SQLite Python. An aggregate function is a database management function that groups the values of numerous rows into a single summary value. Average (i.e., arithmetic mean), sum, max, min, Count are common aggregation functions. SQLite provides us with many aggregate functions used for statistical analysis.
Database for demonstration: To download the database click here.
Max() function
max() function returns the maximum value of all the values from the column we specified.
Syntax: max(name_of_the_column)
Python3
# import the sqlite module import sqlite3 # establishing a connection to the database connection = sqlite3.connect("sales.db") # Obtain a cursor object cursor = connection.cursor() # Find the maximum yearly_sale max_sale = "select max(yearly_sale) from sales1" cursor.execute(max_sale) print("The maximum yearly sale is is:") print(cursor.fetchone()[0]) # Closing database connection connection.close() |
Output:
The maximum yearly sale is is: 98787.0
Min() function
min() function returns the minimum value of the all the values from the column we specified.
Syntax: min(name_of_the_column)
Python3
# import the sqlite module import sqlite3 # establishing a connection to the database connection = sqlite3.connect("sales.db") # Obtain a cursor object cursor = connection.cursor() # minimum yearly sale min_sale = "select min(yearly_sale) from sales1" cursor.execute(min_sale) # Print the minimum score print("The minimum yearly sale is:") # fetching the result print(cursor.fetchone()[0]) # Closing database connection connection.close() |
Output:
The minimum yearly sale is: 25659.0
Avg() function
avg() function returns the average or arithmetic mean of all the values in the column we specify. If any null value is there in the column it’s left out.
Syntax: avg(name_of_the_column)
Python3
# import the sqlite module import sqlite3 # establishing a connection to the database connection = sqlite3.connect("sales.db") # creating a cursor object cursor = connection.cursor() # average value of yearly_sales avg_sale = "select avg(yearly_sale) from sales1" cursor.execute(avg_sale) print("The average yearly sales is:") print(cursor.fetchone()) # Closing database connection connection.close() |
Output:
The average yearly sales is: (66441.75,)
Total() function
total() function returns the total or sum of all values of the column.
Syntax: total(name_of_the_column)
Python3
# import the sqlite module import sqlite3 # establishing a connection to the database connection = sqlite3.connect("sales.db") # creating a cursor object cursor = connection.cursor() # total monthly_sale Total_mon_sale= "select total(monthly_sale) from sales1"cursor.execute(Total_mon_sale) # Print the total score print("The total monthly sale of all items is:") print(cursor.fetchone()[0]) # Closing database connection connection.close() |
Output:
The total monthly sale of all items is: 26230.0
Sum() function
sum() function returns the sum of all values of the column, in case all values are null , it returns null. so, total() function is a comparatively better function.
Syntax: sum(name_of_the_column)
Python3
# import the sqlite module import sqlite3 # establishing a connection to the database connection = sqlite3.connect("sales.db") # creating a cursor object cursor = connection.cursor() # sum of all the yearly sale sum_yearly_sale = "select sum(yearly_sale) from sales1" cursor.execute(sum_yearly_sale) # Print the sum of scores print("The sum of yearly sale is :") print(cursor.fetchone()[0]) # Closing database connection connection.close() |
Output:
The sum of yearly sale is : 265767.0
Count() function
count() function returns the number of nonnull values in a specific column or the whole table.
count of all rows in a table:
count(*)
count of all rows in a specified column:
count(name_of_the_column)
Python3
# import the sqlite module import sqlite3 # establishing a connection to the database connection = sqlite3.connect("sales.db") # creating a cursor object cursor = connection.cursor() # count of all the rows of the database count = "select count(*) from sales1" cursor.execute(count) print("The count of all rows of the table :") print(cursor.fetchone()[0]) # Closing database connection connection.close() |
Output:
The count of all rows of the table : 4
