From: Rick James
Date: September 17 2012 11:12pm
Subject: RE: Making myself crazy...
List-Archive: http://lists.mysql.com/mysql/228174
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148B889C7B@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

If the subquery-version is not too slow, live with it.

If necessary, make your non-grouped SELECT a subquery and apply GROUP BY ou=
tside.  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...
>=20
> 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...
>=20
> I have the following (simplified) table structure:
>=20
> s.timelog --> s.projects --> s.profit_centres
>=20
> s.product.sales --> s.products --> s.profit_centres
>=20
> (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.)
>=20
> What I want to do is figure out productivity: sales per hour worked per
> Profit Centre.
>=20
> 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.
>=20
> But needing to do two levels of indirection has stymied me: how can I
> group $/hour by Profit Centre?
>=20
> You can see the SQL here if you wish:
> 	http://www.ecoreality.org/wiki/Template:Annual_gross_productivity
> _for
>=20
> Then click on the "Source" link to see the code. (You won't be able to
> change it without logging in.)
>=20
> I'm using CASE to pivot tables to separate out monetary versus
> barter/trade income.
>=20
> Thanks for any help offered!
>=20
> ----------------
> :::: 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 ::::
>=20
>=20
>=20
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql