As title suggests, I created a result with a formula but now need to break it up into having the formula run for every "owner" but seperate the formula into years. I have the dates 2010-2019. Here is the code
SELECT * FROM finaltab;
Select finaltab.OWNER as OWNER, SUM(finaltab.shares) AS SSUM FROM finaltab WHERE trancode='S' GROUP BY OWNER Order by year; Select finaltab.OWNER as OWNER, SUM(finaltab.shares) AS PSUM FROM finaltab WHERE trancode='P' GROUP BY OWNER Order by year;
SELECT finaltab.OWNER, SSUM, PSUM FROM (SELECT finaltab.OWNER AS OWNER, SUM(finaltab.shares) AS SSUM FROM finaltab WHERE trancode='S' GROUP BY OWNER) AS STable INNER JOIN (SELECT finaltab.OWNER AS OWNER, SUM(finaltab.shares) AS PSUM FROM finaltab WHERE trancode='P' GROUP BY OWNER) aS PTable ON (Stable.owner = Ptable.owner);
SELECT STable.OWNER, SSUM, PSUM, (PSUM - SSUM) / (PSUM + SSUM) as "IOF" FROM (SELECT finaltab.OWNER as OWNER, SUM(finaltab.shares) AS SSUM From finaltab WHERE trancode='S' GROUP BY OWNER) AS STable INNER JOIN (SELECT finaltab.OWNER as OWNER, SUM(finaltab.shares) AS PSUM From finaltab WHERE trancode='P' GROUP BY OWNER) AS PTable ON (STable.OWNER = PTable.OWNER);
Here is the table. Also I would like some direction on if I want to take the "IOF" or formula and implement it into a formula with absolute value for the "IOF" in the numerator. Thank you [1]: https://i.stack.imgur.com/P9GtU.png
