CODEWITHSUNDEEP
mysqlsql
Bobface
Bobface

Reputation: 2952

MySQL calculation within query

My current query looks like this:

SELECT * FROM `Entrys` WHERE `Timestamp` >  1469308755 AND `Timestamp` < 1469308765 AND (`Exchange` = 1 OR `Exchange` = 2) AND `Market` = 1

It gives me the following result:

enter image description here

For each timestamp value (1469308756 and 1469308761) , I want to calculate the spreads between the prices like this:

For each timestamp-value
  For each Exchange
    For each Other-Exchange
      Divide Exchanges price where type = 2 by Other-Exchanges price where type = 1

This is the best I can explain it. If you don't understand it please leave a comment and I'll try to explain it better.

With the above data it should work like this:

Format: EnId.Field

943.Price / 940.Price
944.Price / 939.Price
961.Price / 985.Price
962.Price / 957.Price

The calculated numbers should be the output of the query.

My SQL skills are way to low for that. Is it even possible to do such calculation inside a single query?

Upvotes: 1

Views: 76

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

You need to use a JOIN. A JOIN is useful when you want data from two different rows to be referenced in the same expression (either an expression in the WHERE clause or an expression in the select-list).

So you need to join a row to another row that satisfies these conditions:

  • the same timestamp
  • a different exchange
  • the first row is type=2 and the second row is type=1

When you're doing a self-join like this one, you must use table aliases. I will use "numerator" and "denominator" for the two rows.

SELECT numerator.timestamp, numerator.price / denominator.price AS spread
FROM Entrys AS numerator
JOIN Entries AS denominator ON numerator.timestamp=denominator.timestamp
     AND numerator.exchange <> denominator.exchange
     AND numerator.type = 2 AND denominator.type = 1;

Upvotes: 1

Related Questions