SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    May 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT min, max, open, close (trades)

    I have a table (trades) with some stock-trades

    It has the following fields
    id (autogenerate, key)
    stock (int)
    tradetime (datetime)
    price (decimal (10,4))
    stockcount (int)

    Now it would like to make an SELECT from 'trades' that will give me
    min (the smallest price value)
    max (the largest price value)
    open (the first price value, - i.e. the price for the trade with smallest tradetime and should there be more smallest id)
    close (the last, - like open but opposite)

    GROUP BY stock, LEFT(tradetime,10)
    WHERE "some where clause that will select fewer dates and/or stocks, - I'll will vary (but generally include a stock = N and I don't think I'll get into troubles with this part)

    MIN and MAX is quite simple
    But how do I do open and close?

  2. #2
    SitePoint Member
    Join Date
    May 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually even better would be to get the weighted average if there are more trades at the same date and time, i.e. (count1*price1 + count2*price2 + count3*price3 ...) / (count1+count2+count3+...)

    But it's not that important if it is much harder to do and/or put a much bigger stress on the database

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT daytradedata.stock
         , daytradedata.tradedate
         , daytradedata.opentime
         , opentrade.price  AS openprice
         , daytradedata.closetime
         , closetrade.price  AS closeprice
      FROM ( SELECT stock                      
                  , DATE(tradetime) AS tradedate
                  , MIN(price) AS minprice
                  , MAX(price) AS maxprice
                  , MIN(tradetime)  AS opentime             
                  , MAX(tradetime)  AS closetime      
               FROM trades       
              WHERE stock = 937           
                AND tradetime >= '2008-04-01'
                AND tradetime  < '2008-05-01' 
             GROUP
                 BY stock                      
                  , DATE(tradetime)  ) AS daytradedata
    INNER
      JOIN trades AS opentrade
        ON opentrade.stock = daytradedata.stock
       AND opentrade.tradetime = daytradedata.opentime
    INNER
      JOIN trades AS closetrade
        ON closetrade.stock = daytradedata.stock
       AND closetrade.tradetime = daytradedata.closetime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    May 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, - you're the man!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •