Articles

Python MySQL COUNT Aggregate function

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.

SQL COUNT

 

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.

SQL 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  
Please Share this page

Related Articles

Like every other website we use cookies. By using our site you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service. Learn more Got it!