CODEWITHSUNDEEP
mysqlsql
ejectamenta
ejectamenta

Reputation: 1087

sql database maths operation on data

I have a database with rows and columns of data, the row,col data is some summed data (ie. 1 3 5 7 from original data = 1 2 2 2).

I want to get the original data by subtraction eg. val=r1c2-r1c1 etc across the rows and columns. Is this possible in sql without having to do lots of individual select statements for each row/column?

I would like to do this for all rows and columns in database, like the following pseudocode

a[ 1,3,5,7;
   2,5,6,7 ]; 

for(i=0;i<size(a,1); i++) 
     for(j=0;j<size(a,2)-1; j++)
          b(i,j)=a(i,j+1)-a(i,j);

Upvotes: 0

Views: 58

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Try this.

SELECT col1,
       col2 - col1 col2,
       col3 - col2 col3,
       col4 - col3 col4
FROM   (SELECT 1 col1,
               3 col2,
               5 col3,
               7 col4) a

Update: Same query will work for more than one row 

SELECT col1,
       col2 - col1 col2,
       col3 - col2 col3,
       col4 - col3 col4
FROM   (SELECT 1 col1,3 col2,5 col3,7 col4
        UNION ALL
        SELECT 1  col1,3  col2,9  col3,11 col4
        UNION ALL
        SELECT 1 col1,3 col2,5 col3,7 col4) a

Upvotes: 2

Related Questions