Write sql query to get the below required output

I have table with col1 values , col2 1st row value should be same col1 1st row, then col 2 row onwards row each should added each row values eg: col1 values 10,20,30. Etc, in col2 values display like 10, 30,60 etc. how we can write sql query ?

Questions by hraju_tt

Showing Answers 1 - 8 of 8 Answers

thilak

  • Jun 17th, 2013
 

Insert values into table(col1(10,20,30),col2(20,30,60),col3(30,40,60));

  Was this answer useful?  Yes

Sujatha N

  • May 15th, 2014
 

Option 1:
If you need col2 to be created with manipulation on col1 when the table is created, you can refer to Virtual Columns concept in Oracle

Option 2:
If the real intention is only to display the sum while querying, here is the working code using analytical function – Lag

Code
  1. SELECT Col1 , (Col1 + LAG(Col1, 1 ,0) over(PARTITION BY Col1 ORDER BY Col1 nulls LAST))  AS Col2

    2. 

  2. FROM Sample_Table;

    3. 

  3.  


Good Luck!!

  Was this answer useful?  Yes

vijaya lakshmi

  • May 22nd, 2014
 

INSERT INTO TABLE VALUES(col1(10,20,30),col2(30,40,60));

  Was this answer useful?  Yes

pratik

  • Jun 9th, 2014
 

We can use the analytical function LAG to achieve the desired result

Code
  1. SELECT Col1, NVL(LAG(Col1, 1) over(ORDER BY Col1),0) AS col2, 

    2. 

  2. (Col1 + NVL(LAG(Col1, 1) 

    3. 

  3. over (ORDER BY Col1),0))  AS Col3

    4. 

  4. FROM Sample_Table;

  Was this answer useful?  Yes

kranthiswaroop

  • Jun 16th, 2014
 

/* Formatted on 6/16/2014 5:08:51 PM (QP5 v5.114.809.3010) */
SELECT s,
CASE
WHEN rnk = 1
THEN
s
WHEN rnk > 1
THEN
(SELECT SUM (s)
FROM (SELECT s, RANK () OVER (ORDER BY s ASC) rnk1
FROM temp1)
WHERE rnk1 <= rnk)
END
s_add
FROM (SELECT s, RANK () OVER (ORDER BY s ASC) rnk FROM temp1)

  Was this answer useful?  Yes

kranthiswaroop

  • Jun 16th, 2014
 

Hi Hraju,
Please check this code given, its works as per your scenario.

SELECT s,
CASE
WHEN rnk = 1
THEN
s
WHEN rnk > 1
THEN
(SELECT SUM (s)
FROM (SELECT s, RANK () OVER (ORDER BY s ASC) rnk1
FROM temp1)
WHERE rnk1 <= rnk)
END
s_add
FROM (SELECT s, RANK () OVER (ORDER BY s ASC) rnk FROM temp1)

  Was this answer useful?  Yes

Vinod Kumar G M

  • Nov 7th, 2014
 

SELECT COL_NAME , SUM(COL_NAME) OVER ( ORDER BY COL_NAME) FROM TABLE_NAME

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions