Explain the use of COUNT() AND SUM() in MySQL using Python?

The COUNT() and SUM() functions are MySQL aggregate functions used to perform arithmetic operations on column values. These functions are essential for data analysis and reporting in database applications.

The COUNT() function returns the number of rows that satisfy a specified condition, while the SUM() function calculates the total of numerical values in a column, ignoring NULL values.

Syntax

COUNT() Function:

SELECT COUNT(column_name) FROM table_name WHERE condition

SUM() Function:

SELECT SUM(column_name) FROM table_name WHERE condition

Steps to Use COUNT() and SUM() with MySQL in Python

• Import the MySQL connector module

• Establish connection using connect()

• Create a cursor object using cursor() method

• Write SQL queries with COUNT() or SUM() functions

• Execute queries using execute() method

• Fetch results and close the connection

Sample Data

Let's work with a "Students" table containing student names and their marks ?

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

Example

This example demonstrates counting students with marks above 80 and calculating the total marks ?

import mysql.connector

# Establish database connection
db = mysql.connector.connect(
    host="your_host",
    user="your_username", 
    password="your_password",
    database="your_database_name"
)

cursor = db.cursor()

# COUNT() - Number of students with marks > 80
query1 = "SELECT COUNT(marks) FROM Students WHERE marks > 80"
cursor.execute(query1)
count_result = cursor.fetchone()
print("Number of students with marks > 80:", count_result[0])

# SUM() - Total of all marks
query2 = "SELECT SUM(marks) FROM Students"
cursor.execute(query2)
sum_result = cursor.fetchone()
print("Sum of all marks:", sum_result[0])

# Close the connection
db.close()

Output

Number of students with marks > 80: 2
Sum of all marks: 377

Key Points

• COUNT() returns the number of non-NULL values in the specified column

• SUM() calculates the total of numerical values, ignoring NULL entries

• Use fetchone() for single aggregate results instead of fetchall()

• Access the result value using index [0] from the returned tuple

• Always close database connections to free up resources

Conclusion

COUNT() and SUM() functions are powerful tools for data analysis in MySQL. Use COUNT() to count records meeting specific conditions and SUM() to calculate totals of numerical columns. Always handle database connections properly by closing them after use.