From: Jan Steinman
Date: September 19 2012 4:25am
Subject: Re: Making myself crazy...
List-Archive: http://lists.mysql.com/mysql/228192
Message-Id: <535C947B-3568-42CF-AA0F-D99219D8E4B7@Bytesmiths.com>
MIME-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

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 =3D proj.id
LEFT JOIN
  s_profit_centre pc ON proj.profit_centre =3D pc.ID
WHERE proj.private =3D 'no' AND YEAR(tl.`in`) =3D '{{{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 =3D prod.ID
  LEFT JOIN
    s_profit_centre pc ON prod.profit_centre =3D pc.ID
  WHERE YEAR(sales.`Date`) =3D '{{{1}}}'
  GROUP BY `Profit Centre`) mt
    ON mt.pcid =3D tt.pcid
WHERE mt.tot !=3D 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=3DInnoDB  DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_bin =
COMMENT=3D'EcoReality time log.' AUTO_INCREMENT=3D19462 ;
--
-- 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=3DInnoDB  DEFAULT CHARSET=3Dutf8 COMMENT=3D'EcoReality (and =
other) projects that require resources.' AUTO_INCREMENT=3D145 ;

--
-- 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=3DInnoDB DEFAULT CHARSET=3Dutf8;

--
-- 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','oun=
ces','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=3DInnoDB DEFAULT CHARSET=3Dlatin1;

--
-- 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=3DInnoDB  DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_bin =
COMMENT=3D'list of EcoReality farm products' AUTO_INCREMENT=3D186 ;

--
-- 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.
>=20
> If necessary, make your non-grouped SELECT a subquery and apply GROUP =
BY outside.  Thus:
>=20
> SELECT ... FROM ( SELECT non-grouped... ) GROUP BY ...
>=20
> 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)
>=20
>> -----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
>=20

----------------
:::: She can't sing in more languages than most people can't speak in. =
-- JWS
:::: Jan Steinman, EcoReality Co-op ::::