SQL Division - Rows - Netezza

Question

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

Will the 10 rows always have Slno values 1 to 10? – Jul 21 '13 at 14:57 — , Jul 21 '13 at 14:57

score 1 · Answer 1 · answered Jul 21 '13 at 15:00

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.

score 1 · Answer 2 · answered Jul 21 '13 at 15:03

1

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

answered Jul 21 '13 at 15:03

Robert Co

1,715
8
14

If there is no Slno column..can I use an index or something similar for the SQL ? – Gallop Jul 21 '13 at 15:14
What if there is no serial number and we have only the Ads column...and we are to frame the query...Thanks – Gallop Jul 21 '13 at 16:56
@Moonwalk - One way would be to use a temp table. See my answer below. – unlimit Jul 21 '13 at 17:15

score 0 · Answer 3 · answered Jul 21 '13 at 17:14

0

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

answered Jul 21 '13 at 17:14

unlimit

3,672
2
26
34

Also the issue I have in the database is that SlNo is not a primary key and data is as follows Slno Ads 1 282542 2 2492266 3 2259286 4 2072619 5 1915098 1 11567811 2 10097 3 5900 4 2572 5 18569 . Please find the updated Slno column – Gallop Jul 22 '13 at 06:24
@Moonwalk - I think temp table is possible in Netezza. The answer that I provided is in tsql. You had tagged your question with tsql. Did you mean to say that the SlNo repeats in the second comment? – unlimit Jul 22 '13 at 06:31
Yes first five rows will always have serial number 1,2,3,4,5 then the next five rows will have 1,2,3,4,5....I mean to say we may not have Slno from 1-10 – Gallop Jul 22 '13 at 06:36
Okay, but you are looking for the result as `Second 1/ First 1, Second 2/ First 2` and so on so forth? Are you going to have data in order? I mean is it guaranteed that the second set will be after the first set? – unlimit Jul 22 '13 at 06:40
Yes..the data is guaranteed to be be in same order...Thanks – Gallop Jul 22 '13 at 06:42
Then I guess the temp table solution should work, just ignore the slno and load the data into temp table. But I don't know about netezza. – unlimit Jul 22 '13 at 06:46

SQL Division - Rows - Netezza

3 Answers3