Databases
StackOverflow2013
Postsdbo
POHow to use Group By and self-join to return min, max, open, and close daily price restult set?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to use Group By and self-join to return min, max, open, and close daily price restult set?
    primarykey
    data
    text
    <p><strong>SOLVED</strong></p> <p>All hail StackOverlow!</p> <p>While I was gone, people left 2 solutions (thanks guys--what is the protocol for handing out karma for two working solutions?)</p> <p>Here is the solution that I came back to post. it is derived from yet ANOTHER StackOver solution:</p> <p><a href="https://stackoverflow.com/questions/1379565/mysql-first-and-last-record-of-a-grouped-record-aggregate-functions">How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?</a></p> <p>...and my adaptation is:</p> <pre><code>SELECT DATE_FORMAT(`DTE`, "%m/%d/%Y") AS trading_day, MIN(`PRICE`) AS min_price, MAX(`PRICE`) AS max_price, SUBSTRING_INDEX( GROUP_CONCAT( PRICE ORDER BY DTE ASC ) , ',', 1 ) AS opn_price, SUBSTRING_INDEX( GROUP_CONCAT( PRICE ORDER BY DTE DESC ) , ',', 1 ) AS cls_price FROM `CHART_DATA` GROUP BY trading_day ; </code></pre> <p>The data the "Q" above starts with is the data with which I am trying to end. Hopefully, this helps someone else since I suspect that my log is fairly common.</p> <p>I am willing to bet that one of these three solutions has a performance advantage. If anyone happens to know the inner workings of MySQL and query optimization and cares to recommend the "preferred" solution, that will be useful to know in the future.</p> <p><strong>END SOLVED</strong></p> <p><strong>UPDATE #2</strong></p> <p>Tryhing to come at it from yet another direction using this:</p> <p><a href="http://forums.mysql.com/read.php?65,363723,363723" rel="nofollow noreferrer">http://forums.mysql.com/read.php?65,363723,363723</a></p> <p>I get:</p> <pre><code>SELECT DATE_FORMAT(`DTE`, "%m/%d/%Y") AS trading_day, MIN(`PRICE`) AS min_price, MAX(`PRICE`) AS max_price, (SELECT opn_price FROM (SELECT DATE_FORMAT(`DTE`, "%m/%d/%Y") AS a_day, PRICE AS opn_price, MIN(DTE) AS opn FROM `CHART_DATA` GROUP BY a_day ORDER BY opn ASC LIMIT 1) AS tblO) AS opnqt, (SELECT cls_price FROM (SELECT DATE_FORMAT(`DTE`, "%m/%d/%Y") AS a_day, PRICE AS cls_price, MIN(DTE) AS cls FROM `CHART_DATA` GROUP BY a_day ORDER BY cls DESC LIMIT 1) AS tblC) AS clsqt FROM `CHART_DATA` cht GROUP BY trading_day; </code></pre> <p>This suffers from a similar dysfunction as the query in the first UPDATE below; the 'clsqt' (cls_price) value returned is the last closing price found in the data. Blech.</p> <p>Plus, we are starting to get into "hideously complex" query space again and that CANNOT be good for performance.</p> <p>But if anyone sees the fix for the 'clsqt' value, I would accept it gladly and solve the performance issue at a later day. :)</p> <p><strong>END UPDATE #2</strong></p> <p><strong>UPDATE</strong></p> <p>So close...here's where I am today:</p> <pre><code>SELECT DATE_FORMAT(`cht1`.`DTE`, "%m/%d/%Y") AS trading_day, MIN(`cht1`.`PRICE`) AS min_price, MAX(`cht1`.`PRICE`) AS max_price, MIN(cht1.DTE) AS opn_date1, MIN(cht1.DTE) AS opn_date2, `cht2`.`PRICE` AS opn_price, MAX(cht1.DTE) AS cls_date1, MAX(cht3.DTE) AS cls_date3, `cht3`.`PRICE` AS cls_price FROM `CHART_DATA` cht1 LEFT JOIN `CHART_DATA` cht2 ON cht2.DTE = cht1.DTE LEFT JOIN `CHART_DATA` cht3 ON cht3.DTE = cht1.DTE GROUP BY trading_day HAVING opn_date1 = opn_date2 AND cls_date1 = cls_date3 ; </code></pre> <p>This retuns everything correctly BUT the correct 'cls_price' (it is returnign the same value for 'cls_price' as 'opn_price').</p> <p>However, 'cls_date1' and 'cls_date3' are the correct values, so I must be close.</p> <p>Anyone see what I am not?</p> <p><strong>END UPDATE</strong></p> <p>I have been poring over SO with regard to left joins and self joins...and I must admit that I am not grokking.</p> <p>I found this "Q" that seems very close to what I want: <a href="https://stackoverflow.com/questions/4002413/left-join-with-condition-for-right-table-in-mysql">left join with condition for right table in mysql</a></p> <p>What I am trying to do is retrieve open, close, min, and max price days from a single table (sample data below).</p> <p>Min and Max are easy:</p> <pre><code>SELECT DATE_FORMAT(`DTE`, "%m/%d/%Y") AS trading_day, MIN(`PRICE`) AS min_price, MAX(`PRICE`) AS max_price FROM `CHART_DATA` GROUP BY trading_day; </code></pre> <p>I want the results returned group by date, somthing like:</p> <pre><code>'trading_day' 'opn_price' 'min_price' 'max_price' 'cls_price' </code></pre> <p>Okay, so I try 'baby steps' with just one join...</p> <pre><code>SELECT DATE_FORMAT(`cht1`.`DTE`, "%m/%d/%Y") AS trading_day, MIN(`cht1`.`PRICE`) AS min_price, MAX(`cht1`.`PRICE`) AS max_price, `cht2`.`PRICE` AS opn_price FROM `CHART_DATA` cht1 LEFT JOIN `CHART_DATA` cht2 ON cht2.DTE = MIN(cht1.DTE) GROUP BY trading_day; </code></pre> <p>...and I get the message "Invalid use of group function"</p> <p>Of course, removing the "GROUP BY" is no help, since I need to return aggegate columns.</p> <p>I have a really complex solution that gets the open and close results, but not the min and max--and they are in separate result sets. I get the feeling that I have made this more complex than is necessary and that fi I could just grasp what is going on with the self joins cited in the "Q" referenced above, that my overall coding would improvie immeasurably. But I have spent something like 12 hours on this during the past weekend and am more confusted than ever.</p> <p>All insight and explantion and observation is welcome at this point...</p> <pre><code>/* SAMPLE TABLE AND DATA */ CREATE TABLE `CHART_DATA` ( `ID` varchar(10) DEFAULT NULL, `DTE` datetime DEFAULT NULL, `PRICE` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*Data for the table `CHART_DATA` */ INSERT INTO `chart_data` (`id`,`dte`,`price`) VALUES ('1','2011-01-01 00:10:00',0.65), ('2','2011-01-01 06:10:00',0.92), ('3','2011-01-01 12:10:00',0.59), ('4','2011-01-01 18:10:00',0.16), ('5','2011-01-02 00:10:00',0.28), ('6','2011-01-02 06:10:00',0.12), ('7','2011-01-02 12:10:00',0.92), ('8','2011-01-02 18:10:00',0.1), ('9','2011-01-03 00:10:00',0.34), ('10','2011-01-03 06:10:00',0.79), ('11','2011-01-03 12:10:00',1.23), ('12','2011-01-03 18:10:00',1.24), ('13','2011-01-04 00:10:00',1.12), ('14','2011-01-04 06:10:00',0.8), ('15','2011-01-04 12:10:00',0.65), ('16','2011-01-04 18:10:00',0.78), ('17','2011-01-05 00:10:00',0.65), ('18','2011-01-05 06:10:00',1.19), ('19','2011-01-05 12:10:00',0.89), ('20','2011-01-05 18:10:00',1.05), ('21','2011-01-06 00:10:00',0.29), ('22','2011-01-06 06:10:00',0.43), ('23','2011-01-06 12:10:00',0.26), ('24','2011-01-06 18:10:00',0.34), ('25','2011-01-07 00:10:00',0.22), ('26','2011-01-07 06:10:00',0.37), ('27','2011-01-07 12:10:00',1.22), ('28','2011-01-07 18:10:00',1.16), ('29','2011-01-08 00:10:00',0.3), ('30','2011-01-08 06:10:00',1.17), ('31','2011-01-08 12:10:00',0.62), ('32','2011-01-08 18:10:00',0.86), ('33','2011-01-09 00:10:00',0.84), ('34','2011-01-09 06:10:00',1.11), ('35','2011-01-09 12:10:00',0.92), ('36','2011-01-09 18:10:00',1.03), ('37','2011-01-10 00:10:00',1.13), ('38','2011-01-10 06:10:00',0.58), ('39','2011-01-10 12:10:00',1.03), ('40','2011-01-10 18:10:00',0.21), ('41','2011-01-11 00:10:00',0.12), ('42','2011-01-11 06:10:00',1.01), ('43','2011-01-11 12:10:00',0.19), ('44','2011-01-11 18:10:00',1.14), ('45','2011-01-12 00:10:00',0.55), ('46','2011-01-12 06:10:00',0.75), ('47','2011-01-12 12:10:00',0.66), ('48','2011-01-12 18:10:00',1.1), ('49','2011-01-13 00:10:00',0.68), ('50','2011-01-13 06:10:00',0.3), ('51','2011-01-13 12:10:00',0.9), ('52','2011-01-13 18:10:00',0.88), ('53','2011-01-14 00:10:00',0.64), ('54','2011-01-14 06:10:00',1.06), ('55','2011-01-14 12:10:00',1.12), ('56','2011-01-14 18:10:00',0.76), ('57','2011-01-15 00:10:00',0.18), ('58','2011-01-15 06:10:00',1.08), ('59','2011-01-15 12:10:00',0.66), ('60','2011-01-15 18:10:00',0.38), ('61','2011-01-16 00:10:00',1), ('62','2011-01-16 06:10:00',1.18), ('63','2011-01-16 12:10:00',1.15), ('64','2011-01-16 18:10:00',0.58), ('65','2011-01-17 00:10:00',1.04), ('66','2011-01-17 06:10:00',0.81), ('67','2011-01-17 12:10:00',0.35), ('68','2011-01-17 18:10:00',0.91), ('69','2011-01-18 00:10:00',0.14), ('70','2011-01-18 06:10:00',0.13), ('71','2011-01-18 12:10:00',1.03), ('72','2011-01-18 18:10:00',0.16), ('73','2011-01-19 00:10:00',1.05), ('74','2011-01-19 06:10:00',1.13), ('75','2011-01-19 12:10:00',1.21), ('76','2011-01-19 18:10:00',0.34), ('77','2011-01-20 00:10:00',0.63), ('78','2011-01-20 06:10:00',0.62), ('79','2011-01-20 12:10:00',0.19), ('80','2011-01-20 18:10:00',1.21), ('81','2011-01-21 00:10:00',0.83), ('82','2011-01-21 06:10:00',0.99), ('83','2011-01-21 12:10:00',0.83), ('84','2011-01-21 18:10:00',0.21), ('85','2011-01-22 00:10:00',0.8), ('86','2011-01-22 06:10:00',0.69), ('87','2011-01-22 12:10:00',0.87); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USshamelesshacker
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to use Group By and self-join to return min, max, open, and close daily price restult set?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to use Group By and self-join to return min, max, open, and close daily price restult set?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to use Group By and self-join to return min, max, open, and close daily price restult set?
      1. USchanp
      1. VTFavorite
    1. COThe protocol when multiple answers are helpful is to upvote any that are helpful, and then select as accepted answer the one that was the *most* helpful. You may need a few more reputation points before you can upvote answers.
      singulars
      1. POHow to use Group By and self-join to return min, max, open, and close daily price restult set?
      1. USBill Karwin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload