On Mon, Nov 10, 2008 at 4:33 AM, King Simon-NFHD78
<[EMAIL PROTECTED]> wrote:
> I'm no SQL expert, so please take this with a pinch of salt, but as far
> as I know, conditions in the 'WHERE' clause of an SQL statement are
> applied BEFORE any grouping, so you can't use grouping functions (such
> as SUM, MAX etc) in the WHERE clause. Instead, you add a 'HAVING'
> statement on the end to filter the rows AFTER the grouping.
Ahh, that helps a lot.
> BTW, I think the 'no grouping functions in WHERE clause' rule is also
> the reason why your MAX query didn't work. The fix that Mike gave you
> turned that part of your query into a subquery that only produced that
> single value. This statement:
I see. That is why the select worked in my first test case but not
the second. In the max case, there was only a single value to return.
In the sum case, there was a sum grouped by (strategy, symbol).
Indeed, the having clause does what I want::
In [39]: q = session.query(Snapshot.strategy, Snapshot.symbol,
sum_pnl).group_by(Snapshot.strategy, Snapshot.symbol).having(sum_pnl<-15000)
In [40]: print q SELECT snapshot.strategy AS snapshot_strategy,
snapshot.symbol AS
snapshot_symbol, sum(snapshot.pnl) AS sum_1
FROM snapshot GROUP BY snapshot.strategy, snapshot.symbol
HAVING sum(snapshot.pnl) < %s
This produces a list of (strategy, symbol, sum(pnl)) as desired.
Now what I'm trying to figure out how to do is get a count over each
strategy of the number of symbols where the sum(pnl)<-15000. So I
need to do one group_by over (strategy, symbol) to get the right sums,
and then one group_by over strategy alone to get the symbol counts
where the threshold criterion is met.
To be honest, I don't really know how to do this in pure SQL, so this
is part sqlalachemy, part SQL question. In the past, I have done
naive sql queries and done the extra logic in python, so this time
around I am trying to be a little more persistent in figuring out the
sql way to do things.
Thanks for your explanation!
JDH
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
