Python MySQL COUNT Aggregate function
In this python tutorial, we will see how to perform COUNT aggregate function in mysql database in xampp through python.
In sql, COUNT() function returns the total number of rows in a table.
Syntax for COUNT()
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
If we want to get count of all rows from all columns, use *.
SELECT COUNT(*) FROM table_name WHERE condition;
Now, let' see how to return total number of 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 COUNT 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 total count of rows in market table.
#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 total number of rows
cursor_obj.execute("SELECT COUNT(*) FROM market")
#fetch the rows
results = cursor_obj.fetchall()
#get the values
for i in results:
print("Total rows: ",i[0])
Output:
Total rows: 11
We can see that total number of rows present in the market table is 11.
Let's see how to use COUNT 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.
COUNT() with GROUP BY will return total number of rows in a group.
Syntax of GROUP BY
SELECT COUNT(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 total count 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 count by grouping market_area column
cursor_obj.execute("SELECT market_area,COUNT(*) 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]," & ", "Total-Count: ",i[1])
print()
Output:
We can see that 4 groups are returned by grouping similar values in market_area column and returned total number of rows in each groups.
Market Area: bengal & Total-Count: 2
Market Area: guntur & Total-Count: 1
Market Area: hyderabad & Total-Count: 5
Market Area: kakumanu & Total-Count: 3
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 :
Jun 30,2023