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.

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()