Detail Block Minimum values [message #641324] |
Thu, 13 August 2015 06:16 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
knd.prasad
Messages: 35 Registered: December 2009 Location: HYDERABAD
|
Member |
|
|
Hi,
I need to sum detail block minimum two values
data
emp_no empname salary
10001 ramesh 5000
10002 hari 3000
10003 kiran 6000
10004 vamsi 2000
I need to sum of 3000 and 2000
Thanks in Advance
prasad
|
|
|
|
|
Re: Detail Block Minimum values [message #641381 is a reply to message #641379] |
Fri, 14 August 2015 00:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
If those values are stored in a table, then you can run a query similar to this one (you'd probably have to include a condition which would join table values to the ones you see on the screen):SQL> WITH test
2 AS (SELECT 1 empno, 'ramesh' empname, 5000 salary FROM DUAL
3 UNION ALL
4 SELECT 2, 'hari', 3000 FROM DUAL
5 UNION ALL
6 SELECT 3, 'kiran', 6000 FROM DUAL
7 UNION ALL
8 SELECT 4, 'vamsi', 2000 FROM DUAL),
9 ranked
10 AS (SELECT empno,
11 empname,
12 salary,
13 RANK () OVER (ORDER BY salary) rnk
14 FROM test)
15 SELECT SUM (salary)
16 FROM ranked
17 WHERE rnk < 3;
SUM(SALARY)
-----------
5000
SQL>
However, if those records are on the screen, then you'll have to loop through them, "manually" find the lowest two salaries (note that "two" doesn't necessarily mean "only two records" as several people can have the same salary) and sum them.
|
|
|
|
|
|