Use COUNT and SUM in MySQL using Python

These are the functions used to perform the arithmetic operations on the column values in a table.

The COUNT() function is used to return the number of rows which satisfy a certain condition.

The SUM() function is used to return the sum of numerical values in a column in the table.The NULL values are ignored.

Syntax

COUNT()

SELECT COUNT(column_name) FROM table_name WHERE condition

SUM()

SELECT SUM(column_name) FROM table_name

Steps invloved to use count() and sum() functions on a table using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query using the appropriate mysql statements

• execute the SQL query using execute() method

• close the connection

Suppose we have the following table named “Students”.

Students

+----------+-----------+
|    name  |    marks  |
+----------+-----------+
|    Rohit |    62     |
|    Rahul |    75     |
|    Inder |    99     |
|   Khushi |    49     |
|    Karan |    92     |
+----------+-----------+

We want to count the number of students having marks above 80 and we want to get the sum of all the marks obtained by the students.

Example

import mysql.connector
db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")
cursor=db.cursor()

query1="SELECT COUNT(marks) FROM Students WHERE marks>80 "
cursor.execute(query1)
cnt=cursor.fetchall()
print(“Number of students :”,cnt)

query2="SELECT SUM(marks) FROM Students "
cursor.execute(query2)
sum=cursor.fetchall()
print(“Sum of marks :”, sum)

db.close()

Output

Number of students : 2
Sum of marks : 377