CODEWITHSUNDEEP
mysql
Pr0no
Pr0no

Reputation: 4099

Still unresolved: how to calculate price change (using prices from DIFFERENT dates) in MySQL?

Consider the following table:

sp100_id  _date        open  close  bullishness
-----------------------------------------------
1         2011-03-14   100   110    2.23
1         2011-03-15   115   100    1.00
1         2011-03-16   110   110    0.85
2         2011-03-14    90    85    0.99
2         2011-03-15    95    90    0.30
2         2011-03-16    92   100    4.66
3         2011-03-14   200   220    1.50
3         2011-03-15   250   210    1.75
3         2011-03-16   200   150    0.80

I want to calculate the % return and bullishness for each stock between 2011-03-14 and 2011-03-16, rank them by % return DESC, and the return sp100_id, % return and average bullishness. I thought the following query would do the trick:

SELECT 
  sp100_id, 
  AVG(bullishness) as bullishness, 
  ((`close`-`open`) / `open`) as return_pct   
FROM
  stocks 
WHERE _date = BETWEEN '2011-03-14' AND '2011-03-16' 
ORDER BY return_pct DESC

However, it always just seems to return just one row. The expected output was however:

sp100_id return_pct                average bullishness
-----------------------------------------------------------
3        (150-200)/200 = -0.250   (1.50+1.75+0.80)/3 = 4.05
2        (100-90)/90   =  0.110   (0.99+0.30+4.66)/3 = 1.98
1        (110-100)/100 =  0.100   (2.23+1.00+0.85)/3 = 4.08

What am I doing wrong?

Upvotes: 2

Views: 126

Answers (1)

Jocelyn
Jocelyn

Reputation: 11393

You must use GROUP BYin your query:

SELECT 
  sp100_id, 
  AVG(bullishness) as bullishness, 
  (((SELECT `close` FROM stocks s2
   WHERE s2.sp100_id=s1.sp100_id ORDER BY _date DESC LIMIT 1)
    -(SELECT `open` FROM stocks s3
   WHERE s3.sp100_id=s1.sp100_id ORDER BY _date ASC LIMIT 1)) / 
     (SELECT `open` FROM stocks s3
   WHERE s3.sp100_id=s1.sp100_id ORDER BY _date ASC LIMIT 1)) as return_pct
FROM
  stocks s1
WHERE _date BETWEEN '2011-03-14' AND '2011-03-16' 
GROUP BY sp100_id
ORDER BY return_pct DESC

Without GROUP BY, it returns only one result row because AVG groups all records together.

I fixed the computation of return_pct using subqueries with ORDER BY and LIMIT 1.

Documentation: GROUP BY (Aggregate) Functions

SQLfiddle

Upvotes: 3

Related Questions