List:General Discussion« Previous MessageNext Message »
From:Rick James Date:September 17 2012 11:12pm
Subject:RE: Making myself crazy...
View as plain text  
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
> 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

Thread
Making myself crazy...Jan Steinman16 Sep
  • RE: Making myself crazy...Rick James17 Sep
    • Re: Making myself crazy...Jan Steinman19 Sep
      • RE: Making myself crazy...Rick James19 Sep