SQL Alchemy How to query Join and Sum Distinct

Issue

How do I query join two tables and sum distinct values in a column?

Given Parent:

[![enter image description here](https://i.stack.imgur.com/iYLj1.png)](https://i.stack.imgur.com/iYLj1.png)

Given Child:

enter image description here

Expected Result:

enter image description here

from app import db_con
from sqlalchemy import ForeignKey
from sqlalchemy.dialects import mssql

class Parent(db_con.Model):
    __tablename__ = "parent"
    ID = db_con.Column(
        "id", mssql.INTEGER, nullable=False, primary_key=True
    )
    COST = db_con.Column("cost", mssql.DECIMAL)
    CATEGORY_ID = db_con.Column("category_id", mssql.INTEGER, ForeignKey("child.category_id"))
    CATEGORY = db_con.relationship("Child", foreign_keys=[ID], uselist=False)


class Child(db_con.Model):
    __tablename__ = "child"
    CATEGORY_ID = db_con.Column("category_id", mssql.INTEGER, nullable=False, primary_key=True)
    NAME = db_con.Column("name", mssql.NVARCHAR(None))


Solution

#...
from sqlalchemy.sql import func

class CategoryCost(Base):
    __tablename__ = "category_costs"
    id = Column(
        Integer, nullable=False, primary_key=True
    )
    cost = Column(Numeric)
    category_id = Column(Integer, ForeignKey("categories.id"))
    category = relationship("Category", backref="category_costs")


class Category(Base):
    __tablename__ = "categories"
    id = Column(Integer, nullable=False, primary_key=True)
    name = Column(Text())

Base.metadata.create_all(engine)

def get_cats():
    objs = []
    for index, name in enumerate(["water", "electricity", "gas", "subscription"]):
        category_id = index + 1
        objs.append(Category(id=index+1, name=name))
    return objs

def get_cat_costs():
    objs = []
    for cost, cat_id in [(32, 1), (51, 2), (6, 3), (68, 4), (36, 4), (89, 3), (4, 4), (83, 2), (56, 1)]:
        objs.append(CategoryCost(category_id=cat_id, cost=cost))
    return objs

with Session(engine) as session:
    for cat in get_cats():
        session.add(cat)
    session.commit()
    for cat_cost in get_cat_costs():
        session.add(cat_cost)
    session.commit()
    q = session.query(Category.name, func.sum(CategoryCost.cost)).join(Category.category_costs).group_by(Category.name).order_by(Category.name)
    for name, total_cost in q.all():
        print (f"{name:25} {total_cost:10}")

Answered By – Ian Wilson

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published