MYSQL select arithmetic with column results

Question

I have gotten to this result from my table for two underliers TQQQ and SQQQ

select TradeDate, Stock_Short, LastPrice*Volume as USDVolume from aktien 
where Stock_Short ="TQQQ" OR Stock_Short = "SQQQ" order by TradeDate desc

which gives this result

looks like this

2018-10-30  SQQQ     1131165621.6
2018-10-30  TQQQ     1100960774.12
2018-10-29  SQQQ     996285358.9
2018-10-29  TQQQ     1263999527.63
2018-10-26  SQQQ     831584079.95
2018-10-26  TQQQ     1496815364.75
2018-10-25  SQQQ     608926709.68

Now i want to subtract TQQQ-SQQQ for each calendar day in the table.

2018-10-30 -30,204,847
2018-10-29 etc ...

How can i do this?

Madhur Bhaiya · Accepted Answer · 2018-10-31 11:40:25Z

3

You can do a Group By on TradeDate, and you can use either If() or Case .. When to conditionally evaluate the Sum() as per the Stock_Short values.
Eventually, you can simply calculate the difference using the conditionally summed up values.

Try the following query:

SELECT 
  TradeDate, 
  (
   SUM(IF(Stock_Short = "TQQQ", LastPrice * Volume, 0)) - 
   SUM(IF(Stock_Short = "SQQQ", LastPrice * Volume, 0)) 
  ) AS difference 
FROM aktien 
WHERE Stock_Short IN ("TQQQ","SQQQ")
GROUP BY TradeDate 
ORDER BY TradeDate DESC

Using Case .. When, the query would look as follows:

SELECT 
  TradeDate, 
  (
   SUM(CASE WHEN Stock_Short = "TQQQ" THEN LastPrice * Volume ELSE 0 END) - 
   SUM(CASE WHEN Stock_Short = "SQQQ" THEN LastPrice * Volume ELSE 0 END) 
  ) AS difference 
FROM aktien 
WHERE Stock_Short IN ("TQQQ","SQQQ")
GROUP BY TradeDate 
ORDER BY TradeDate DESC

edited Oct 31, 2018 at 11:40

answered Oct 31, 2018 at 10:45

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Walter Schrabmair Over a year ago

why so complicated? Why does not work my solution? `SELECT a.Stock_Short,a.TradeDate,b.Stock_Short,a.lastprice - b.lastprice from aktien a join aktien b on a.TradeDate = b.TradeDate where a.Stock_Short = 'TQQQ' and b.Stock_Short = 'SQQQ' ;

Madhur Bhaiya Over a year ago

@NoobPython I have optimized the code considerably. I realized that there is no need to use Sub-select query; Please check the updated answer, and let me know if it still works or not.

Madhur Bhaiya Over a year ago

@NoobPython and what's the result ?

AndiAna Over a year ago

see below. thanks. i think the join is slightly more intensive calculation wise. but safer.

Walter Schrabmair · Accepted Answer · 2018-10-31 11:33:17Z

1

SELECT a.Stock_Short,a.TradeDate,b.Stock_Short,a.lastprice,a.volume,b.lastprice,b.volume,(a.volume * a.lastprice) - (b.volume * b.lastprice) from aktien a join aktien b on a.TradeDate = b.TradeDate where a.Stock_Short = 'TQQQ' and b.Stock_Short = 'SQQQ' ;

edited Oct 31, 2018 at 11:33

answered Oct 31, 2018 at 11:24

Walter Schrabmair

1,3532 gold badges15 silver badges34 bronze badges

Comments

AndiAna · Accepted Answer · 2018-10-31 12:05:23Z

0

I have tried Madhur's above solution as well as the solution by Walter.

I have modified it slightly

SELECT a.TradeDate,(a.lastprice*a.Volume) - (b.lastprice*b.Volume) as USDflow 
from aktien a join aktien b on a.TradeDate = b.TradeDate where a.Stock_Short =    
'TQQQ' and b.Stock_Short = 'SQQQ' order by TradeDate desc ;

This gives the same result as Madhurs takes 0.029s for the query. therefore slightly longer.

But i think the advantage of this solution is the treatment of the TradeDate. If a TradeDate is missing for one the JOIN will ignore this TradeDate whereas Madhurs solution will calculate the sum/difference still.

Thanks to both!

answered Oct 31, 2018 at 12:05

AndiAna

97411 silver badges34 bronze badges

Collectives™ on Stack Overflow

MYSQL select arithmetic with column results

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related