[MediaWiki-l] Pivot Query Help

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[MediaWiki-l] Pivot Query Help

Jan Steinman
I'm using MySQL 5.0.92-log.

I'm trying to do a pivot-sort-of-thing. I've tried a few things from the O'Reilly "SQL Cookbook," but I seem to be having a mental block.

I have a table of farm harvests. Each harvest has a date, quantity, and foreign keys into product and harvester tables:
----------------
CREATE TABLE s_product_harvest (
  id int(10) unsigned NOT NULL auto_increment,
  `date` datetime NOT NULL COMMENT 'Date and time of harvest.',
  product int(11) unsigned NOT NULL default '53',
  quantity decimal(10,3) NOT NULL default '1.000',
  units enum('kilograms','grams','pounds','ounces','liters','each','cords','bales') character set utf8 NOT NULL default 'kilograms',
  who1 int(5) unsigned NOT NULL default '2' COMMENT 'Who harvested this resource?',
  notes varchar(255) character set utf8 NOT NULL,
  PRIMARY KEY  (id),
  KEY product (product),
  KEY `date` (`date`),
  KEY who1 (who1),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='historical list of EcoReality farm products harvested';
----------------

What I want is a report with years as columns, and rows of:
        first harvest (MIN(date)),
        last harvest (MAX(date)),
        days of harvest (DATEDIFF(MAX(date), MIN(date))) and
        total (SUM(quantity)).

        first/last 2007 2008 2009 ...
        first Aug 5 Sep 27 Aug 7
        last Oct 1 Nov 24 Oct 16
        days 57 108 82
        kg 10.17 16.7 46.53

This is my first attempt, and it appears to be giving me a row per year, with the first sequential harvest date for each year. I can get the data I want by making each one a separate column, but that's ugly and I want them in rows.

SELECT
  'first_last' AS `First/Last`,
  CASE WHEN YEAR(harvest.date)='2007' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2007',
  CASE WHEN YEAR(harvest.date)='2008' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2008',
  CASE WHEN YEAR(harvest.date)='2009' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2009',
  CASE WHEN YEAR(harvest.date)='2010' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2010',
  CASE WHEN YEAR(harvest.date)='2011' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2011',
  CASE WHEN YEAR(harvest.date)='2012' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2012',
  CASE WHEN YEAR(harvest.date)='2013' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2013',
  CASE WHEN YEAR(harvest.date)='2014' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2014'
FROM
  s_product_harvest harvest
WHERE harvest.product = 4 /* product ID for tomatoes */
GROUP BY YEAR(harvest.date)

Using an example from "SQL Cookbook" on page 372, I tried to select from a subquery, grouped by a rank, but I kept getting one result row, and I can't figure out how to get the literal row headers.

Any ideas?

:::: Compared to those on pasteurized milk, children who received raw certified milk had better weight gain and greater protection against rachitis. -- Ron Schmid
:::: Jan Steinman, EcoReality Co-op ::::

_______________________________________________
MediaWiki-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l