Calculations between two different rows same Table

[hecto1one]

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.

[ck9663]

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:

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

[amitpatel66]

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:

[code=sql]

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

[/code]

[hecto1one]

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?????

[amitpatel66]

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:

[code=sql]
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
[/code]