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:j...@bytesmiths.com] > Sent: Sunday, September 16, 2012 3:45 PM > To: mysql@lists.mysql.com > 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 > left.) > > 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: > http://www.ecoreality.org/wiki/Template:Annual_gross_productivity > _for > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
