By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,940 Members | 1,300 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
4 Replies


ck9663
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
  1. select column1, (sr.month1/month1 - 1), (sr.month2/month2 - 1)
  2. (select top 1 sr.* from YourSalesTable sr where sr.column1 > YourSalesTable.column1) 
  3. 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

amitpatel66
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
  1.  
  2. SELECT * FROM table1
  3. UNION ALL
  4. SELECT 'Grown', (t.month1 / x.month1 - 1),(t.month1 / x.month1 - 1)
  5. ,(t.month2 / x.month2 - 1),(t.month3 / x.month3 - 1),(t.month4 / x.month4 - 1)
  6. ,(t.month5 / x.month5 - 1),(t.month6 / x.month6 - 1),(t.month7 / x.month7 - 1)
  7. ,(t.month8 / x.month8 - 1),(t.month9 / x.month9 - 1),(t.month10 / x.month10 - 1)
  8. ,(t.month11 / x.month11 - 1),(t.month12 / x.month12 - 1),(t.total/x.total - 1) FROM
  9. (SELECT d.* FROM table1 d WHERE column1 = 'Year07') t
  10. (SELECT y.* FROM table1 y WHERE column1 = 'Year08') x
  11.  
  12.  
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

amitpatel66
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
  1. SELECT * FROM table1
  2. UNION ALL
  3. SELECT 'Grown', (t.month1 / x.month1 - 1),(t.month1 / x.month1 - 1)
  4. ,(t.month2 / x.month2 - 1),(t.month3 / x.month3 - 1),(t.month4 / x.month4 - 1)
  5. ,(t.month5 / x.month5 - 1),(t.month6 / x.month6 - 1),(t.month7 / x.month7 - 1)
  6. ,(t.month8 / x.month8 - 1),(t.month9 / x.month9 - 1),(t.month10 / x.month10 - 1)
  7. ,(t.month11 / x.month11 - 1),(t.month12 / x.month12 - 1),(t.total/x.total - 1) FROM
  8. (SELECT d.* FROM table1 d WHERE column1 = 'Year07') t,
  9. (SELECT y.* FROM table1 y WHERE column1 = 'Year08') x
  10.  
Mar 4 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.