> -----Original Message-----
> From: sqlalchemy@googlegroups.com
> [mailto:[EMAIL PROTECTED] On Behalf Of John Hunter
> Sent: 10 November 2008 14:07
> To: sqlalchemy@googlegroups.com
> Subject: [sqlalchemy] Re: select where field=max(field)
>
>
> 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.
>
I think the query should look something like this:
SELECT strategy, COUNT(*)
FROM (SELECT snapshot.strategy AS strategy,
snapshot.symbol AS snapshot_symbol,
sum(snapshot.pnl) AS sum_1
FROM snapshot
GROUP BY snapshot.strategy, snapshot.symbol
HAVING sum(snapshot.pnl) < 1500) AS strategies
GROUP BY strategy
Run that by hand on your database and see if you get the results you
expect. The nested query gets the list of strategies that match the
original criteria, and the outer query uses that to produce the counts.
(Note that there are other ways to get the same result. For example, you
could JOIN your snapshot table to the subquery, which might be useful if
you wanted other columns from it in the outer query)
It should be fairly easy to build that query with SA's underlying
expression language. I'm not certain how to do it through session.query,
but I'm sure it's possible.
Simon
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
