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

Given Parent:

Given Child:

Expected Result:

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))

CodePudding user response：

#...
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}")

CodePudding user response：

from sqlalchemy.sql import text

DB_engine = create_engine("mssql://user_name:password@host_name/database_name")

query = text(
         f""" CREATE TABLE DE_STAGING.RESULT_TABLE AS  
SELECT CAT.NAME, SUM(COST) SUM FROM DE_STAGING.COST_TABLE CT
INNER JOIN DE_STAGING.CAT_TABLE CAT ON CT.CATEGORY_ID = CAT.ID
GROUP BY CAT.NAME""")
with engine.connect() as conn:

    result = conn. execute(query)```