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 ::::
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
