Python MySQL – GROUP BY and HAVING Clause

In this article, we will see how to perform groupby() and HAVING() operations on SQL using Python. Here we will consider a college database to perform group by operation on the department with respect to student strength.

GROUP BY

The GROUP BY statement groups rows that have the same values into single based on the aggregate function used. Aggregate functions are (COUNT(), MAX(), MIN(), SUM(), AVG()).

Syntax: SELECT aggregate_function(column1),column2,…,columnn

FROM table_name

GROUP BY column_name;

Database in use:

Example:

Python3

# Establish connection to MySQL database
import mysql.connector

database = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="sravan"
)

# Creating cursor object
cur_object = database.cursor()

# Execute the query
find = "SELECT  department,sum(strength) from \
college_data GROUP BY(department)";
cur_object.execute(find)

# fetching all results
data = cur_object.fetchall()
print("Total departments with count : ")
print(" ")
for res in data:
    print(res[0],res[1],sep="--")

# Close database connection
database.close()

Output:

GROUP BY Having

Having Clause is basically like the aggregate function with the GROUP BY clause. The HAVING clause is used instead of WHERE with aggregate functions. While the GROUP BY Clause groups rows that have the same values into summary rows. The having clause is used with the where clause in order to find rows with certain conditions. The having clause is always used after the Group By clause.

Syntax: SELECT aggregate_function (column_names),column1,column2,…,columnn FROM table_name

GROUP BY column_name

HAVING aggregate_function(column_name) condition;

Database in use:

Example:

Python3

# Establish connection to MySQL database
import mysql.connector

# give connection with xampp
database = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="sravan"
)

# Creating cursor object
cur_object = database.cursor()

find = "SELECT  department,sum(strength) from college_data\
GROUP BY(department) HAVING sum(strength)<=400 ";

# Execute the query
cur_object.execute(find)

# fetching all results
data = cur_object.fetchall()
print("Total departments with count less than 400 : ")
print(" ")
for res in data:
    print(res[0],res[1],sep="--")

# Close database connection
database.close()

Output: