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