459,940 Members | 1,300 Online
Need help? Post your question and get tips & solutions from a community of 459,940 IT Pros & Developers. It's quick & easy.

# Calculations between two different rows same Table

 P: 2 Hi I am trying to solve this, I hope somebody can help me. I have a table which has sales per month per year Table1 Column1 Month1 Month2 Month3 Month4 .... Total ---------------------------------------------------------------------------------------- Year07 Year08 and I would like to calculate the grown per month an total, so I would like to create an additional row Column1 Month1 Month2 Month3 Month4 .... Total ---------------------------------------------------------------------------------------- Year07 Year08 Grown where Grown is calculated as " (Second Row/First Row - 1)" this by every "Month" and Total. Does anyone give me any idea? Thanks. Mar 3 '08 #1
4 Replies

 Expert 2.5K+ P: 2,878 Hi I am trying to solve this, I hope somebody can help me. I have a table which has sales per month per year Table1 Column1 Month1 Month2 Month3 Month4 .... Total ---------------------------------------------------------------------------------------- Year07 Year08 and I would like to calculate the grown per month an total, so I would like to create an additional row Column1 Month1 Month2 Month3 Month4 .... Total ---------------------------------------------------------------------------------------- Year07 Year08 Grown where Grown is calculated as " (Second Row/First Row - 1)" this by every "Month" and Total. Does anyone give me any idea? Thanks. I'm assuming column1 is the year column. try something like this: Expand|Select|Wrap|Line Numbers select column1, (sr.month1/month1 - 1), (sr.month2/month2 - 1) (select top 1 sr.* from YourSalesTable sr where sr.column1 > YourSalesTable.column1)  from YourSalesTable I did not really test this code, so you have to change it as necessary. I just hope you got what am trying to say. Good luck. -- CK Mar 3 '08 #2

 Expert 100+ P: 2,367 Hi I am trying to solve this, I hope somebody can help me. I have a table which has sales per month per year Table1 Column1 Month1 Month2 Month3 Month4 .... Total ---------------------------------------------------------------------------------------- Year07 Year08 and I would like to calculate the grown per month an total, so I would like to create an additional row Column1 Month1 Month2 Month3 Month4 .... Total ---------------------------------------------------------------------------------------- Year07 Year08 Grown where Grown is calculated as " (Second Row/First Row - 1)" this by every "Month" and Total. Does anyone give me any idea? Thanks. Try this: Expand|Select|Wrap|Line Numbers   SELECT * FROM table1 UNION ALL SELECT 'Grown', (t.month1 / x.month1 - 1),(t.month1 / x.month1 - 1) ,(t.month2 / x.month2 - 1),(t.month3 / x.month3 - 1),(t.month4 / x.month4 - 1) ,(t.month5 / x.month5 - 1),(t.month6 / x.month6 - 1),(t.month7 / x.month7 - 1) ,(t.month8 / x.month8 - 1),(t.month9 / x.month9 - 1),(t.month10 / x.month10 - 1) ,(t.month11 / x.month11 - 1),(t.month12 / x.month12 - 1),(t.total/x.total - 1) FROM (SELECT d.* FROM table1 d WHERE column1 = 'Year07') t (SELECT y.* FROM table1 y WHERE column1 = 'Year08') x     Mar 4 '08 #3

 P: 2 Thanks I feel this last answer is close. I just try it but I got error because of the last SELECT after the FROM. That is what I am looking for, I mean is it possible to add two select sentences after the FROM so they can be the two sources to calculate????? Mar 4 '08 #4

 Expert 100+ P: 2,367 Thanks I feel this last answer is close. I just try it but I got error because of the last SELECT after the FROM. That is what I am looking for, I mean is it possible to add two select sentences after the FROM so they can be the two sources to calculate????? Oops, a small syntax error. Here it goes: Expand|Select|Wrap|Line Numbers SELECT * FROM table1 UNION ALL SELECT 'Grown', (t.month1 / x.month1 - 1),(t.month1 / x.month1 - 1) ,(t.month2 / x.month2 - 1),(t.month3 / x.month3 - 1),(t.month4 / x.month4 - 1) ,(t.month5 / x.month5 - 1),(t.month6 / x.month6 - 1),(t.month7 / x.month7 - 1) ,(t.month8 / x.month8 - 1),(t.month9 / x.month9 - 1),(t.month10 / x.month10 - 1) ,(t.month11 / x.month11 - 1),(t.month12 / x.month12 - 1),(t.total/x.total - 1) FROM (SELECT d.* FROM table1 d WHERE column1 = 'Year07') t, (SELECT y.* FROM table1 y WHERE column1 = 'Year08') x   Mar 4 '08 #5