Using SQLite Aggregate functions in Python
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 sqlite3
connection = sqlite3.connect( "sales.db" )
cursor = connection.cursor()
max_sale = "select max(yearly_sale) from sales1"
cursor.execute(max_sale)
print ( "The maximum yearly sale is is:" )
print (cursor.fetchone()[ 0 ])
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 sqlite3
connection = sqlite3.connect( "sales.db" )
cursor = connection.cursor()
min_sale = "select min(yearly_sale) from sales1"
cursor.execute(min_sale)
print ( "The minimum yearly sale is:" )
print (cursor.fetchone()[ 0 ])
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 sqlite3
connection = sqlite3.connect( "sales.db" )
cursor = connection.cursor()
avg_sale = "select avg(yearly_sale) from sales1"
cursor.execute(avg_sale)
print ( "The average yearly sales is:" )
print (cursor.fetchone())
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 sqlite3
connection = sqlite3.connect( "sales.db" )
cursor = connection.cursor()
Total_mon_sale = "select total(monthly_sale) from sales1"
cursor.execute(Total_mon_sale)
print ( "The total monthly sale of all items is:" )
print (cursor.fetchone()[ 0 ])
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 sqlite3
connection = sqlite3.connect( "sales.db" )
cursor = connection.cursor()
sum_yearly_sale = "select sum(yearly_sale) from sales1"
cursor.execute(sum_yearly_sale)
print ( "The sum of yearly sale is :" )
print (cursor.fetchone()[ 0 ])
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 sqlite3
connection = sqlite3.connect( "sales.db" )
cursor = connection.cursor()
count = "select count(*) from sales1"
cursor.execute(count)
print ( "The count of all rows of the table :" )
print (cursor.fetchone()[ 0 ])
connection.close()
|
Output:
The count of all rows of the table :
4
Last Updated :
07 Feb, 2022
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...