Thanks for your help, Rick.
The solution that seems to be working was to have both the FROM and LEFT JOIN
be grouped subqueries. I realize this is probably not the best way of doing
things, but it seems fast enough at present.
Here is the query, followed by the table definitions, resulting in the
following data:
http://www.EcoReality.org/wiki/2012_gross_productivity
-- (The following query is from a template, in which "{{{1}}}" is replaced by a
year, such as "2012".)
SELECT
pc AS `Profit Centre`,
CONCAT('$', FORMAT(prp, 2)) AS Paid,
CONCAT('$', FORMAT(prt, 2)) AS Trade,
CONCAT('$', FORMAT(prtot, 2)) AS `Both`
FROM
(SELECT
tt.`Profit Centre` AS pc,
mt.Paid / tt.Hours AS prp,
mt.Trade / tt.Hours AS prt,
mt.tot / tt.Hours AS prtot
FROM
(SELECT
SUM(TIMESTAMPDIFF(MINUTE, tl.`in`, tl.`out`)/60) AS Hours,
pc.ID AS pcid,
pc.name AS `Profit Centre`
FROM s_timelog tl
LEFT JOIN
s_project proj ON tl.project_id = proj.id
LEFT JOIN
s_profit_centre pc ON proj.profit_centre = pc.ID
WHERE proj.private = 'no' AND YEAR(tl.`in`) = '{{{1}}}'
GROUP BY `Profit Centre`) tt
LEFT JOIN
(SELECT
SUM(case when sales.Type NOT IN('barter', 'work trade') then `Total` else 0
end) AS Paid,
SUM(case when sales.Type IN('barter', 'work trade') then `Total` else 0
end) AS Trade,
SUM(`Total`) AS tot,
pc.ID AS pcid,
pc.name AS `Profit Centre`
FROM s_product_sales_log sales
LEFT JOIN
s_product prod ON sales.Product = prod.ID
LEFT JOIN
s_profit_centre pc ON prod.profit_centre = pc.ID
WHERE YEAR(sales.`Date`) = '{{{1}}}'
GROUP BY `Profit Centre`) mt
ON mt.pcid = tt.pcid
WHERE mt.tot != 0) xx
--"s_timelog" and "s_product_sales_log" are the two "ends" of the query, each
related by an intermediate table to the "centre" of the query,
"s_profit_centre":
-- s_timelog.project_id --> s_project.id, s_project.profit_centre -->
s_profit_centre.ID
-- s_product_sales_log.Product --> s_product.ID, s_product.profit_centre -->
s_profit_centre.ID
----------------
-- 18,739 rows, growing by ~100 records monthly
CREATE TABLE `s_timelog` (
`id` int(10) unsigned NOT NULL auto_increment,
`mw_user_id` int(11) unsigned NOT NULL default '2',
`in` datetime NOT NULL,
`out` datetime NOT NULL,
`project_id` int(11) unsigned NOT NULL,
`activity_id` int(11) unsigned NOT NULL,
`accomplishment` varchar(255) character set utf8 NOT NULL,
`Paid` enum('yes','no') character set utf8 NOT NULL default 'no' COMMENT
'hours that are compensated',
`Class_B_qualified` enum('yes','no') character set utf8 NOT NULL default 'no',
`updated` timestamp NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `mw_user_id` (`mw_user_id`),
KEY `in` (`in`),
KEY `project_id` (`project_id`),
KEY `activity_id` (`activity_id`),
KEY `out` (`out`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='EcoReality time
log.' AUTO_INCREMENT=19462 ;
--
-- RELATIONS FOR TABLE `s_timelog`:
-- `user_id`
-- `mw_user` -> `user_id`
-- `mw_user_id`
-- `mw_user` -> `user_id`
-- `project_id`
-- `s_project` -> `id`
-- `activity_id`
-- `s_activity` -> `id`
----------------
-- 143 rows, growing by one or two a month
CREATE TABLE IF NOT EXISTS `s_project` (
`id` int(6) unsigned NOT NULL auto_increment,
`profit_centre` int(10) unsigned NOT NULL default '0',
`profit_centre_amortized` int(3) unsigned default '0' COMMENT 'for capital
projects',
`name` varchar(62) NOT NULL,
`URL` varchar(255) default NULL,
`need` varchar(255) NOT NULL,
`description` text NOT NULL,
`super_id` int(11) unsigned NOT NULL default '0',
`type` enum('capital','operations') NOT NULL default 'operations',
`duration` enum('fixed','ongoing') NOT NULL default 'fixed',
`status` enum('abandoned','agreed','cancelled','completed','on
hold','ongoing','requested') NOT NULL default 'requested',
`start` date NOT NULL,
`end` date NOT NULL,
`completed` date NOT NULL,
`depends_on_id` int(6) unsigned NOT NULL default '0',
`budget_expense` decimal(7,2) NOT NULL,
`budget_effort` decimal(7,2) NOT NULL,
`effort_units` enum('minutes','hours','days','weeks','months') NOT NULL
default 'hours',
`steward_id` int(5) unsigned NOT NULL default '0',
`private` enum('yes','no') NOT NULL default 'no',
`updated` timestamp NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`notes` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `duration` (`duration`),
KEY `status` (`status`),
KEY `super_id` (`super_id`,`depends_on_id`,`steward_id`),
KEY `depends_on_id` (`depends_on_id`,`steward_id`),
KEY `steward_id` (`steward_id`),
KEY `profit_centre` (`profit_centre`),
KEY `profit_centre_amortized` (`profit_centre_amortized`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='EcoReality (and other) projects
that require resources.' AUTO_INCREMENT=145 ;
--
-- RELATIONS FOR TABLE `s_project`:
-- `steward`
-- `mw_user` -> `user_id`
-- `super_id`
-- `s_project` -> `id`
-- `depends_on_id`
-- `s_project` -> `id`
-- `steward_id`
-- `mw_user` -> `user_id`
-- `profit_centre`
-- `s_profit_centre` -> `ID`
-- `profit_centre_amortized`
-- `s_profit_centre` -> `ID`
----------------
-- 12 records, growing by one or two a year
CREATE TABLE IF NOT EXISTS `s_profit_centre` (
`ID` int(3) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`steward` int(5) unsigned NOT NULL,
`updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`notes` text NOT NULL,
PRIMARY KEY (`ID`),
KEY `steward` (`steward`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- RELATIONS FOR TABLE `s_profit_centre`:
-- `steward`
-- `mw_user` -> `user_id`
----------------
-- 3,090 rows, growing by a few hundred monthly
CREATE TABLE `s_product_sales_log` (
`Venue` enum('EcoReality grounds','farm gate','delivered','Tuesday
market','roadside','Saturday market','store','subscription','Yellow House
kitchen') character set utf8 NOT NULL default 'farm gate',
`Seller` int(11) unsigned NOT NULL default '212',
`Who` int(11) unsigned NOT NULL default '0',
`Date` date NOT NULL,
`Quantity` decimal(8,3) NOT NULL,
`Unit`
enum('bale','box','bunch','dozen','each','grams','kilograms','liters','ounces','pints','pounds','quarts')
character set utf8 collate utf8_bin NOT NULL default 'each',
`Total` decimal(8,2) NOT NULL,
`Type` enum('barter','cash','check','invoice','PayPal','work trade')
character set utf8 NOT NULL default 'cash',
`Product` int(11) unsigned NOT NULL,
KEY `Product` (`Product`),
KEY `Date` (`Date`),
KEY `Seller` (`Seller`),
KEY `Who_2` (`Who`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- RELATIONS FOR TABLE `s_product_sales_log`:
-- `Seller`
-- `mw_user` -> `user_id`
-- `Who`
-- `mw_user` -> `user_id`
-- `Product`
-- `s_product` -> `ID`
----------------
-- 186 records, growing by ~1 monthly
CREATE TABLE IF NOT EXISTS `s_product` (
`ID` int(10) unsigned NOT NULL auto_increment,
`super` int(11) unsigned default NULL COMMENT 'generalization',
`name` varchar(31) character set utf8 NOT NULL,
`units`
enum('kilograms','grams','pounds','ounces','liters','each','cords','bales')
character set utf8 NOT NULL default 'kilograms' COMMENT 'preferred unit',
`profit_centre` int(3) unsigned NOT NULL default '3',
`tax_qualified` enum('yes','no') character set utf8 NOT NULL default 'yes',
`tax_livestock_born` enum('yes','no') character set utf8 NOT NULL default
'no',
`tax_poultry_egg` enum('yes','no') character set utf8 NOT NULL default 'no',
`is_value_added` enum('yes','no') character set utf8 NOT NULL default 'no',
`plant_ID` int(5) unsigned default NULL,
`description` varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Name` (`name`),
KEY `Description` (`description`),
KEY `is_value_added` (`is_value_added`),
KEY `profit_centre` (`profit_centre`),
KEY `super` (`super`),
KEY `plant_ID` (`plant_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='list of
EcoReality farm products' AUTO_INCREMENT=186 ;
--
-- RELATIONS FOR TABLE `s_product`:
-- `super`
-- `s_product` -> `ID`
-- `profit_centre`
-- `s_profit_centre` -> `ID`
-- `plant_ID`
-- `s_plants` -> `ID`
On 17 Sep 12, at 16:12, Rick James wrote:
> 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
>
----------------
:::: She can't sing in more languages than most people can't speak in. -- JWS
:::: Jan Steinman, EcoReality Co-op ::::
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
