List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:November 4 2013 5:31pm
Subject:Pivot Query Help
View as plain text  
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 ::::
Thread
Pivot Query HelpJan Steinman4 Nov