Open In App

Using SQLite Aggregate functions in Python

Improve
Improve
Like Article
Like
Save
Share
Report

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


Last Updated : 07 Feb, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads