sum over a field on a query

Question

this should be a asked-before question, I searched but I could not find any answer on that. Sorry if it is duplicated. I have a query lets say:

my_query=session.query(Item).filter(somefilter)

Now, Item has a column, lets say counter, and I want to find the sum of this column of my_query. I can do that like this:

sum=0
for row in query:
    sum+=row.counter

but I don't this this is the efficient way of doing this specially in a large database. I know that this is possible: sqlalchemy simple example of `sum`, `average`, `min`, `max`, but this requires filtering on qry (borrowed from the page) which I have already given the filtered version my_query. I dont know if it is really more efficient to do the filtering again on top of qry v.s. using the for loop on my_query.

Sonorant

#3293252 – Handyman

Answer 1

I had the same question. I asked on irc.freenode.org#sqlalchemy and inklesspen pointed me to Query#with_entities().

sum = my_query.with_entities(func.sum(Item.counter)).scalar()

Baseburner

Answer 2

There is a whole bunch of SQL "group" functions in sqlalchemy.func:

from sqlalchemy import func
my_query = session.query(func.sum(Item.counter)).filter(somefilter)

Throughway

Hi Ketouem, I think in this case, you should filter on top of the query, while what I was asking was summing on an already existed query... – Sonorant