Hello.
Perhaps something like this is what you are looking for?
def q_union():
s = session
q1 = s.query(
Movement.customer_id.label('customer_id'),
Movement.document_id.label('document_id'),
Movement.debt.label('debt_moved'),
literal(0.00).label('debt_total')
)
q2 = s.query(
Debt.customer_id.label('customer_id'),
Debt.document_id.label('document_id'),
literal(0.00).label('debt_moved'),
Debt.debt.label('debt_total'),
)
q3 = q1.union(q2).subquery()
q4 = session.query(
q3.c.customer_id,
q3.c.document_id,
func.sum(q3.c.debt_moved),
func.sum(q3.c.debt_total),
).group_by(q3.c.customer_id, q3.c.document_id)
return q4
Call to `print q_union()` prints the following:
SELECT
anon_1.customer_id AS anon_1_customer_id,
anon_1.document_id AS anon_1_document_id,
sum(anon_1.debt_moved) AS sum_1,
sum(anon_1.debt_total) AS sum_2
FROM (
SELECT
anon_2.customer_id AS customer_id,
anon_2.document_id AS document_id,
anon_2.debt_moved AS debt_moved,
anon_2.debt_total AS debt_total
FROM (
SELECT
movement.customer_id AS customer_id,
movement.document_id AS document_id,
movement.debt AS debt_moved,
%(param_1)s AS debt_total
FROM movement
UNION
SELECT
debt.customer_id AS customer_id,
debt.document_id AS document_id,
%(param_2)s AS debt_moved,
debt.debt AS debt_total
FROM debt
) AS anon_2
) AS anon_1
GROUP BY anon_1.customer_id, anon_1.document_id
I have attached a complete python script that you can run to verify if it's
correct.
HTH,
Ladislav Lenart
On 21.3.2013 09:07, Alexey Vihorev wrote:
> Hi!
>
>
>
> I have this query:
>
>
>
> q1 =(s.query()
>
> .add_columns(
>
> Movement.customer_id,
>
> Movement.document_id,
>
> func.sum(Movement.debt).label('debt_moved'),
>
> literal(0.00).label('debt_total')
>
> )
>
> .group_by(Movement.customer_id, Movement.document_id)
>
> )
>
>
>
> q2 =(s.query().
>
> add_columns(
>
> Debt.customer_id,
>
> Debt.document_id,
>
> literal(0.00).label('debt_moved'),
>
> Debt.debt.label('debt_total'),
>
> )
>
> )
>
>
>
> q3 =q1.union(q2)
>
>
>
> The problem is that I need to SUM() the resulting query (columns 'debt_moved',
> 'debt_moved' should be summed, grouped by customer_id, document_id), but so
> far all my attempts to use add_columns() and group_by() on q3 have failed. How
> should I handle this this?
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email
> to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
