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
