Join with sum and count of grouped rows in SQLAlchemy
Last Updated :
24 Apr, 2025
SQLAlchemy is a popular Python ORM (Object-Relational Mapping) library that provides a convenient way to interact with databases. One of the common tasks when working with databases is to perform joins between tables and calculate aggregate values based on grouped rows. In this article, we will explore how to use SQLAlchemy to join tables and calculate sums and counts of grouped rows.
Join with the sum and count of grouped rows in SQLAlchemy
Creating Table
First, let's consider a simple example scenario where we have two tables - orders and order_items. The orders table contains information about orders such as order ID, customer ID, and order date. The order_items table contains information about the items that are part of each order such as item ID, order ID, item name, and item price.
Orders Table
Order_items TableOur goal is to join these two tables and calculate the total amount spent by each customer and the number of items purchased by each customer. We can achieve this by grouping the order_items table by customer ID and calculating the sum of item prices and the count of items for each group. Here's how we can do it using SQLAlchemy.
Join with Sum
In this example, we have used SQLAlchemy and Postgresql. At first, we are making a connection of SQLAlchemy with Postgres in the function create_engine() Â then we are defining the table structures. Then we are using a sum and join function to get the total sum of item_price in the table.
Python3
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
# Define the database connection
engine = create_engine('postgresql://postgres:root@localhost:5432/test')
# Define the session
Session = sessionmaker(bind=engine)
session = Session()
# Define the base model
Base = declarative_base()
# Define the orders table
class Order(Base):
__tablename__ = 'orders'
id = Column(Integer, primary_key=True)
customer_id = Column(Integer)
order_date = Column(String)
# Define the order items table
class OrderItem(Base):
__tablename__ = 'order_items'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey('orders.id'))
item_name = Column(String)
item_price = Column(Integer)
order = relationship(Order, backref='order_items')
total_revenue = session.query(func.sum(
OrderItem.item_price)).join(Order).scalar()
# Print the result
print(f"Total revenue: {total_revenue}")
Output:
Total revenue: 2600
Join with Count
In this example, we have used SQLAlchemy and Postgresql. At first, we are making a connection of SQLAlchemy with Postgres in the function create_engine() Â then we are defining the table structures. Then we are counting the total order related to all order_id and print it.
Output:
Order 2: 2 items
Order 3: 1 items
Order 1: 1 items
Join with sum and count
In this example, we have used SQLAlchemy and Postgresql. At first, we are making a connection of SQLAlchemy with Postgres in the function create_engine() Â then we are defining the table structures. Then we are querying which customer spend how much total money and bought how many total items and printed it.
Python3
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
# Define the database connection
engine = create_engine('postgresql://postgres:root@localhost:5432/test')
# Define the session
Session = sessionmaker(bind=engine)
session = Session()
# Define the base model
Base = declarative_base()
# Define the orders table
class Order(Base):
__tablename__ = 'orders'
id = Column(Integer, primary_key=True)
customer_id = Column(Integer)
order_date = Column(String)
# Define the order items table
class OrderItem(Base):
__tablename__ = 'order_items'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey('orders.id'))
item_name = Column(String)
item_price = Column(Integer)
order = relationship(Order, backref='order_items')
# Define the query to join the tables and calculate sums and counts
query = session.query(Order.customer_id,
func.sum(OrderItem.item_price).label('total_spent'),
func.count(OrderItem.id).label('item_count')
).join(OrderItem).group_by(Order.customer_id)
# Execute the query and print the results
for row in query:
print(f"Customer {row.customer_id}: total spent =
{row.total_spent}, item count = {row.item_count}")
Output:
Customer 30: total spent = 700, item count = 1
Customer 10: total spent = 500, item count = 1
Customer 20: total spent = 1400, item count = 2
Similar Reads
How to GroupBy and Sum SQL Columns using SQLAlchemy?
In this article, we are going to see how to use GroupBy and Sum in SQLAlchemy. Installing SQLAlchemySQLAlchemy is available via pip install package. pip install sqlalchemyHowever, if you are using a flask you can make use of its own implementation of SQLAlchemy. It can be installed using - pip insta
4 min read
How to count rows with SELECT COUNT(*) with SQLAlchemy?
In this article, we will see how to select the count of rows using SQLAlchemy in Python. Since we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the code implementations changes with change in the database except for the SQL connectors
3 min read
Returning distinct rows in SQLAlchemy with SQLite
In this article, we are going to see how to return distinct rows in SQLAlchemy with SQLite in Python. Installation SQLAlchemy is available via pip install package. pip install sqlalchemy However, if you are using flask you can make use of its own implementation of SQLAlchemy. It can be installed usi
3 min read
Group by and count function in SQLAlchemy
In this article, we are going to see how to perform Groupby and count function in SQLAlchemy against a PostgreSQL database in Python. Group by and count operations are performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In PostgreSQ
2 min read
How to use sum and order by in SQLAlchemy query?
In this article, we are going to see how to perform the sum and count function in SQLAlchemy against a PostgreSQL database in python. SUM and count operations are performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In PostgreSQL, Gr
3 min read
Python SQLAlchemy - func.count with filter
In this article, we are going to see how to perform filter operation with count function in SQLAlchemy against a PostgreSQL database in python Count with filter operations is performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In Po
3 min read
How to use avg and sum in SQLAlchemy Query?
In this article, we are going to see how to use avg and sum in SQLAlchemy query using Python. Installing SQLAlchemy SQLAlchemy is available via the pip install package. pip install sqlalchemy However, if you are using flask you can make use of its own implementation of SQLAlchemy. It can be installe
2 min read
How to get all rows with keys provided in a list using SQLalchemy?
In this article, we are going to get all the rows with keys provided in a list using SQLAlchemy. Database used: Note: For this post, we are going to get "name", "class" and "dob" keys from the student table. Installation: Syntax to install SQLAlchemy: pip install sqlalchemy pymysql Note: pymysql is
2 min read
Sqlalchemy core, insert multiple rows from a tuple instead of dict
SQLAlchemy is a popular Python library used for working with databases. SQLAlchemy provides an Object-Relational Mapping (ORM) layer and a Core layer. The ORM layer allows developers to work with databases using Python objects, while the Core layer provides a lower-level interface for SQL-oriented d
3 min read
Grouping Data with ROLLUP in SQL
Grouping data is a common operation in SQL when you want to aggregate data based on certain criteria. The MySQL provides the ROLLUP extension to the GROUP BY clause which allows you to generate subtotals and totals for the groups of rows. This article will give an overview of using the ROLLUP extens
4 min read