Python MySQL AVG Aggregate function
In this python tutorial, we will see how to perform AVG aggregate function in mysql database in xampp through python.
In sql, AVG() function returns the total average of the columns having numeric value.
Syntax for AVG()
SELECT AVG(column_name)
FROM table_name
WHERE condition;
Now, let' see how to return average values in a column using python
Before that make sure that you have created a database named - tutorialsinhand and table inside it market with the following records.
Steps:
1. Import mysql.connector module
Syntax:
import mysql.connector
2. Create the connection object to connect with xampp server using the below credentials.
Syntax:
connection_obj = mysql.connector.connect(
host="localhost",
user="root",
password=""
)
3. Create cursor object to execute the query.
Syntax:
connection_obj.cursor()
4. Write sql query to select columns by applying AVG aggregate function on a column.
5. Fetch the resulted rows using fetchall().
Syntax:
cursor.fetchall()
6. Display using for loop
Syntax:
for i in results:
print(i)
Note - It will return the values in a tuple.
Example:
In this example, we will return average in ratings column.
#import the module
import mysql.connector
#create the connection by specifying the
#hostname-localhost
#username-root
#password-''(empty) and
#database is tutorialsinhand
connection_obj = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="tutorialsinhand"
)
#creating cursor object
cursor_obj = connection_obj.cursor()
#return average from rating column
cursor_obj.execute("SELECT AVG(rating) FROM market")
#fetch the rows
results = cursor_obj.fetchall()
#get the values
for i in results:
print("Average ratings: ",i[0])
Output:
Average ratings: 5.4545
We can see that average ratings is 5.4545.
Let's see how to use AVG Aggregate function with GROUP BY.
In Sql, GROUP BY statement is mostly used along with the aggregate functions to group the result based on one or multiple columns.
AVG() with GROUP BY will return average values in a group.
Syntax of GROUP BY
SELECT AVG(column_name)
FROM table_name
GROUP BY column_name;
Important points
-
We can specify multiple columns in GROUP BY.
-
We can use more than one aggregate function for grouping the result-set.
-
If ORDER BY is used then it should only be used after GROUP BY.
Example: In this example, we are grouping rating with market_area column, so it will group the values in market_area column and return average rating in each group.
#import the module
import mysql.connector
#create the connection by specifying the
#hostname-localhost
#username-root
#password-''(empty) and
#database is tutorialsinhand
connection_obj = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="tutorialsinhand"
)
#creating cursor object
cursor_obj = connection_obj.cursor()
#return average from rating column by grouping market_area column
cursor_obj.execute("SELECT market_area,AVG(rating) FROM market GROUP BY market_area")
#fetch the rows
results = cursor_obj.fetchall()
#get the values
for i in results:
print("Market Area: ",i[0]," & ", "Average-Rating: ",i[1])
print()
Output:
We can see that 4 groups are returned by grouping similar values in market_area column and returned average rating in each groups.
Market Area: bengal & Average-Rating: 4.0000
Market Area: guntur & Average-Rating: 8.0000
Market Area: hyderabad & Average-Rating: 5.2000
Market Area: kakumanu & Average-Rating: 6.0000
Would you like to see your article here on tutorialsinhand.
Join
Write4Us program by tutorialsinhand.com
About the Author
Gottumukkala Sravan Kumar 171FA07058
B.Tech (Hon's) - IT from Vignan's University.
Published 1400+ Technical Articles on Python, R, Swift, Java, C#, LISP, PHP - MySQL and Machine Learning
Page Views :
Published Date :
Nov 03,2022