CODEWITHSUNDEEP
sqlt-sqlnetezza
Gallop
Gallop

Reputation: 1405

SQL Division - Rows - Netezza

I have data in a netezza database table column as follows:

    Slno    Ads    
    1       282542
    2       2492266
    3       2259286
    4       2072619
    5       1915098
    6       11567811
    7       10097
    8       5900
    9       2572
    10      18569

Now I need to write a sql query that computes

    Slno 6 / Slno 1 i.e 11567811/282542
    Slno 7 / Slno 2 i.e 10097/2492266
    Slno 8 / Slno 3 i.e 5900/2259286
    Slno 9 / Slno 4 i.e 2572/2072619
    Slno 10/ Slno 5 i.e 18569/1915098

I will always have only 10 rows of data and similar division is required for the data set.

The computed values can be shown in a new column next to the Ads column.

Please let me know if the question is clear and you need any information.

Thanks

Upvotes: 0

Views: 1201

Answers (3)

unlimit
unlimit

Reputation: 3752

If you do not have serial number and have only Ads column, then you could do something like this:

DECLARE @t AS TABLE (slno INT IDENTITY (1,1), Ads DECIMAL)

INSERT INTO @t(Ads)
SELECT Ads FROM AdsTable

SELECT A.Ads, A.Ads/B.Ads ComputedValue 
FROM @t A INNER JOIN @t B ON A.slno = B.slno + 5

This will be the result:

   Ads         ComputedValue
-----------------------------------
11567811    40.9419166000099100310
   10097    0.0040513332044011353
    5900    0.0026114445006077141
    2572    0.0012409420158746011
   18569    0.0096961095463521971

Upvotes: 0

Robert Co
Robert Co

Reputation: 1715

Not sure if this is all you want to do or if there's something you're not telling us.

SELECT n.Slno
     , n.Ads
     , d.Slno
     , d.Ads
     , n.Ads/d.Ads
  FROM AdsTable n
 INNER JOIN AdsTable d
    ON d.Slno = n.Slno - 5
 WHERE n.Slno > 5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can do this with a self join:

select tnum.Ads / tdenom.Ads
from t tnum join
     t tdenom
     on tnum.slno = tdenom.slno + 5;

The +5 not only matches the numerator and denominator rows. It also filters out five of the rows. For instance, "4" can never be a numerator, because there is no row where slno is -1.

Upvotes: 1

Related Questions