Pivot Query Help
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 200720082009...
first Aug 5 Sep 27 Aug 7
lastOct 1 Nov 24 Oct 16
days57 108 82
kg 10.17 16.746.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
Re: Another query question...
The plot thickens...
I noticed that I have similar queries that work as expected. The difference
appears to be that every query that is broken uses WITH ROLLUP, and removing
this makes them behave as expected.
Is this a known bug? Should I submit it as such?
If someone would be so kind as to point me to the bug system, I'll crawl around
in there to see if it's a known problem.
Jan
Begin forwarded message:
From: Jan Steinman j...@bytesmiths.com
Date: 3 November 2013 18:35:47 PST
MySQL 5.0.92-log
I'm trying to form a clickable link using CONCAT, but the link as displayed
points to the NEXT row's URL, not the one from the same row as the other data
displayed!
Is there something I don't understand about this?
Below is the query. {{{1}}} is replaced by a year, like 2013.
The second column is the problem one. When the table is displayed, the link
in the `Product` field points to the NEXT SEQUENTIAL product row! In other
words, if you click on the link for garlic, you'll get the page for
gherkins.
Live example is at: http://www.EcoReality.org/wiki/2013_harvest
If you hover over the link in the `Product` column, you can clearly see that
the page at the link is not the same as that in the `ID` column, but is in
fact the same `ID` as the next sequential row. I am so confused.
SELECT
harvest.product AS ID,
CONCAT('a href=http://www.EcoReality.org/wiki/Product/', s_product.ID,
'', COALESCE(s_product.name, 'TOTAL:'), '/a') AS `Product`,
FORMAT(sum(harvest.quantity), 3) AS `a
href=http://www.EcoReality.org/wiki/Harvest;Harvest/a`,
harvest.units AS Units,
CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
prices.market_type AS `R-W`,
COUNT(*) AS Harvests,
DATE(MIN(harvest.date)) AS Begin,
DATE(MAX(harvest.date)) AS End
FROM
s_product_harvest harvest
INNER JOIN
s_product on s_product.ID = harvest.product AND
s_product.units = harvest.units
LEFT OUTER JOIN
s_product_market_prices prices ON prices.product_ID = harvest.product AND
prices.units = harvest.units AND
year(prices.price_date) = year(harvest.date)
WHERE
year(harvest.date) = {{{1}}}
GROUP BY
s_product.name WITH ROLLUP
Some days I wonder if it might not be better to culturally engineer
humans to enjoy small scale garden farming than to genetically engineer weeds
to save large scale agribusiness. -- Gene Logsdon
Jan Steinman, EcoReality Co-op
The competition for grain between the wealthy car drivers of the world and
the poorest people who are trying to survive is a moral issue that we should
not ignore. The continued increase in biofuels production will result in a
continued decrease in food availability, which we could someday consider to be
a crime against humanity. -- Pat Murphy
Jan Steinman, EcoReality Co-op
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Another query question...
MySQL 5.0.92-log
I'm trying to form a clickable link using CONCAT, but the link as displayed
points to the NEXT row's URL, not the one from the same row as the other data
displayed!
Is there something I don't understand about this?
Below is the query. {{{1}}} is replaced by a year, like 2013.
The second column is the problem one. When the table is displayed, the link in
the `Product` field points to the NEXT SEQUENTIAL product row! In other words,
if you click on the link for garlic, you'll get the page for gherkins.
Live example is at: http://www.EcoReality.org/wiki/2013_harvest
If you hover over the link in the `Product` column, you can clearly see that
the page at the link is not the same as that in the `ID` column, but is in fact
the same `ID` as the next sequential row. I am so confused.
SELECT
harvest.product AS ID,
CONCAT('a href=http://www.EcoReality.org/wiki/Product/', s_product.ID,
'', COALESCE(s_product.name, 'TOTAL:'), '/a') AS `Product`,
FORMAT(sum(harvest.quantity), 3) AS `a
href=http://www.EcoReality.org/wiki/Harvest;Harvest/a`,
harvest.units AS Units,
CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
prices.market_type AS `R-W`,
COUNT(*) AS Harvests,
DATE(MIN(harvest.date)) AS Begin,
DATE(MAX(harvest.date)) AS End
FROM
s_product_harvest harvest
INNER JOIN
s_product on s_product.ID = harvest.product AND
s_product.units = harvest.units
LEFT OUTER JOIN
s_product_market_prices prices ON prices.product_ID = harvest.product AND
prices.units = harvest.units AND
year(prices.price_date) = year(harvest.date)
WHERE
year(harvest.date) = {{{1}}}
GROUP BY
s_product.name WITH ROLLUP
Some days I wonder if it might not be better to culturally engineer humans
to enjoy small scale garden farming than to genetically engineer weeds to save
large scale agribusiness. -- Gene Logsdon
Jan Steinman, EcoReality Co-op
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
2013/11/04 09:32 -0800, Jan Steinman I noticed that I have similar queries that work as expected. The difference appears to be that every query that is broken uses WITH ROLLUP, and removing this makes them behave as expected. Is this a known bug? Should I submit it as such? If someone would be so kind as to point me to the bug system, I'll crawl around in there to see if it's a known problem. There is a bug that I about a half year ago reported, http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH ROLLUP is not always NULL, but is instead the foregoing string in the same field. I suspect that other bugs with missing NULL found by searching for ROLLUP are the same problem. (note link titled Affects Me!) Another, one year ago reported, bug of mine was handled in 5.7.2, but this one not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
Am 04.11.2013 22:55, schrieb h...@tbbs.net: 2013/11/04 09:32 -0800, Jan Steinman I noticed that I have similar queries that work as expected. The difference appears to be that every query that is broken uses WITH ROLLUP, and removing this makes them behave as expected. Is this a known bug? Should I submit it as such? If someone would be so kind as to point me to the bug system, I'll crawl around in there to see if it's a known problem. There is a bug that I about a half year ago reported, http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH ROLLUP is not always NULL, but is instead the foregoing string in the same field. I suspect that other bugs with missing NULL found by searching for ROLLUP are the same problem. (note link titled Affects Me!) Another, one year ago reported, bug of mine was handled in 5.7.2, but this one not would you please quote in a readable way instead signature.asc Description: OpenPGP digital signature
how to create unique key for long varchar?
hi all I want to create a table with a long varchar column, maybe it's the url. according to dns spec, the url's max length is fixed. but I have to deal with url having long params such as a.html?q=fl= I want the url is unique when inserting it. I googled and found http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql this post suggests use md5 of url. But in theory, there will be conflict that two different urls will have the same md5(even it's probablitiy is very small). I want to a absolute correct solution. one method i can come up with is using select ... for update 1. begin transaction 2. select url from tb where md5='' for update 3. if the url is not exist, insert into this url; else do nothing also I need to set the transaction isolation to SERIALIZABLE is this solution correct in multithread/process environment? another method is using trigger to check whether the url exist. I don't know whether this method will be faster than previous one. how to ensure it's correctness in multithread environment? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
