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 ::::