If the subquery-version is not too slow, live with it.
If necessary, make your non-grouped SELECT a subquery and apply GROUP BY outside. Thus:
SELECT ... FROM ( SELECT non-grouped... ) GROUP BY ...
Could you provide that; we might be able to simplify it.
Also provide SHOW CREATE TABLE for each table.
How many rows in each table? (approx)
> -----Original Message-----
> From: Jan Steinman [mailto:Jan@stripped]
> Sent: Sunday, September 16, 2012 3:45 PM
> To: mysql@stripped
> Subject: Making myself crazy...
> I'm having trouble figuring out how to make a query. It seems simple,
> but it's making me crazy right now. Please point out where my thinking
> is addled...
> I have the following (simplified) table structure:
> s.timelog --> s.projects --> s.profit_centres
> s.product.sales --> s.products --> s.profit_centres
> (The arrows refer to many-to-one relationships: each Timelog record
> refers to a single Project, which is in a single Profit Centre. Each
> record has an opaque ID referred to by records in the table to its
> What I want to do is figure out productivity: sales per hour worked per
> Profit Centre.
> I can do this at a gross level -- without grouping by Profit Centres --
> with a subquery: simply sum up the Lales and divide by the sum of the
> labour (Timelog.out - Timelog.in). But I suspect even this can be done
> without a subquery.
> But needing to do two levels of indirection has stymied me: how can I
> group $/hour by Profit Centre?
> You can see the SQL here if you wish:
> Then click on the "Source" link to see the code. (You won't be able to
> change it without logging in.)
> I'm using CASE to pivot tables to separate out monetary versus
> barter/trade income.
> Thanks for any help offered!
> :::: Respond with love and compassion, rather than punishment and
> retaliation, and an angry person will be disarmed. Trade the need to be
> right for a loving relationship. It is a trade you - and everyone
> around you - will benefit from. -- Dean Van Leuven
> :::: Jan Steinman, EcoReality Co-op ::::
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql