Skip to content
Related Articles

Related Articles

Improve Article

Python MySQL – GROUP BY and HAVING Clause

  • Last Updated : 09 May, 2021

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 aggregare_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:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :