Code for SQLAlchemy mailing list post https://groups.google.com/d/msg/sqlalchemy/-/2pEAnubaBukJ

orm-code.py

sift = (func.sum(cast(subquery.c.credo_contacts_is_covalent, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_vdw_clash, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_vdw, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_proximal, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_hbond, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_weak_hbond, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_xbond, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_ionic, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_metal_complex, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_aromatic, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_hydrophobic, INTEGER)),
        func.sum(cast(subquery.c.credo_contacts_is_carbonyl, INTEGER)))
        
 query = session.query(Residue, *sift)

query.sql

SELECT  sq.residue_id,
        sum(sq.is_covalent::int) as is_covalent,
        sum(sq.is_vdw_clash::int) as is_vdw_clash,
        sum(sq.is_vdw::int) as is_vdw,
        sum(sq.is_proximal::int) as is_proximal,
        sum(sq.is_hbond::int) as is_hbond,
        sum(sq.is_weak_hbond::int) as is_weak_hbond,
        sum(sq.is_xbond::int) as is_xbond,
        sum(sq.is_ionic::int) as is_ionic,
        sum(sq.is_metal_complex::int) as is_metal_complex,
        sum(sq.is_aromatic::int) as is_aromatic,
        sum(sq.is_hydrophobic::int) as is_hydrophobic,
        sum(sq.is_carbonyl::int) as is_carbonyl
FROM    (
        SELECT  r.*, cs.*
        FROM    credo.contacts cs
        JOIN    credo.atoms a ON a.atom_id = cs.atom_bgn_id
        JOIN    credo.residues r ON r.residue_id = a.residue_id
        WHERE   cs.is_same_entity = false
                AND cs.is_secondary = false
                AND r.entity_type_bm > 2 
                AND r.chain_id = :chain_id
                AND cs.structural_interaction_type = :structural_interaction_type
        UNION
        SELECT  r.*, cs.*
        FROM    credo.contacts cs
        JOIN    credo.atoms a ON a.atom_id = cs.atom_end_id
        JOIN    credo.residues r ON r.residue_id = a.residue_id
        WHERE   cs.is_same_entity = false
                AND cs.is_secondary = false
                AND r.entity_type_bm > 2 
                AND r.chain_id = :chain_id
                AND cs.structural_interaction_type = :structural_interaction_type
        ) sq
GROUP BY sq.residue_id
ORDER BY sq.residue_id