Home » SQL & PL/SQL » SQL & PL/SQL » Finding Maximum in Group Summing
Finding Maximum in Group Summing Fri, 14 October 2005 07:54
 raajes_n Messages: 33Registered: April 2005 Location: India Member
Sir
I'm using Oracle8i, the below is my sample table with following fields

Sno Value
1 -- 10
1 -- 9
2 -- 110
2 -- 5
3 -- 5
3 -- 8

my aim is to find out an Query (Do not use Analytic fn's) to get the Output as

Sno Value
2 -- 115

ie. I want to sum value by grouping sno and then to find maximum
value

Pls. help me

with regards
rajesh n
Re: Finding Maximum in Group Summing [message #142409 is a reply to message #142388] Fri, 14 October 2005 08:38
 joy_division Messages: 4758Registered: February 2005 Location: East Coast USA Senior Member
``` select * from (select sno,sum(value) value
from foo
group by sno
order by 2 desc)
where rownum <= 1
```

Re: Finding Maximum in Group Summing [message #142613 is a reply to message #142388] Sun, 16 October 2005 20:13
 aketi Messages: 26Registered: October 2005 Junior Member
--Answer1
select sno,sum(Value)
from (select 1 as sno,10 as Value from dual
union select 2,110 from dual
union select 2,5 from dual
union select 3,5 from dual
union select 3,8 from dual)
group by sno
having sum(Value) >= all (select sum(Value)
from (select 1 as sno,10 as Value from dual
union select 2,110 from dual
union select 2,5 from dual
union select 3,5 from dual
union select 3,8 from dual)
group by sno);

--Answer2
select sno,sum(Value)
from (select 1 as sno,10 as Value from dual
union select 2,110 from dual
union select 2,5 from dual
union select 3,5 from dual
union select 3,8 from dual)
group by sno
having sum(Value) = all (select max(sum(Value))
from (select 1 as sno,10 as Value from dual
union select 2,110 from dual
union select 2,5 from dual
union select 3,5 from dual
union select 3,8 from dual)
group by sno);

oraclesqlpuzzle
http://oraclesqlpuzzle.hp.infoseek.co.jp

Re: Finding Maximum in Group Summing [message #142730 is a reply to message #142613] Mon, 17 October 2005 08:04
 raajes_n Messages: 33Registered: April 2005 Location: India Member
Sorry Mr. Aketi
It's toooooooo Bad Query.
Pls. try some other way without using rownum or analytic functions

with regards
rajesh N
Re: Finding Maximum in Group Summing [message #142939 is a reply to message #142730] Tue, 18 October 2005 08:03
 Rahul Desai Messages: 26Registered: March 2002 Junior Member
Select, Select n Select........

-------------------------------------
select * from test1;

SLNO VAL
------ ----------
1 10
1 9
2 110
2 5
3 5
3 8
--------------------------------------

Here is the query
```select b.sl,a.max_val from
( select max(x.grp_val) max_val from
( select slno,sum(val) grp_val from test1 group by slno) x
) a,
(select y.sl,max(y.grp_val) max_val from
( select slno sl,sum(val) grp_val from test1 group by slno) y
group by y.sl
) b
where a.max_val = b.max_val
/

--------------------------------------------
SL    MAX_VAL
--- ----------
2        115
--------------------------------------------
```

Rahul Desai
Re: Finding Maximum in Group Summing [message #143178 is a reply to message #142939] Wed, 19 October 2005 07:07
 raajes_n Messages: 33Registered: April 2005 Location: India Member

Really U r The Best of The Best Mr. Rahul Desai
I'm not well versed in SQl, that's Y I need Orafaq Help
atlast U won, U reflected the idea what I want?
Hat's of U.
Hat's of Orafaq.com too

with regards
Rajesh N
Re: Finding Maximum in Group Summing [message #143192 is a reply to message #143178] Wed, 19 October 2005 07:36
 kojakhu Messages: 6Registered: October 2005 Location: Hungary Junior Member
Excuse me Sir!

What version of Oracle 8i are you using?
8.1.7 has already analytical functions according to the docs.
No offense, I am really just curious and welcome your reply.
Thank you in advance
Re: Finding Maximum in Group Summing [message #143201 is a reply to message #143178] Wed, 19 October 2005 08:18
 joy_division Messages: 4758Registered: February 2005 Location: East Coast USA Senior Member
Just following up here.

My original answer does not use any analytic function and it's a lot easier to remember a simple query like that one. If your goal was to find a query that you can relate to others, I'd have a hard time believeing you would be able to explain it.

Oh, no offense Rahul. That's an excellent answer. I am just wondering why the OP wants an answer to use Oracle, but not use it's built-in features.
Re: Finding Maximum in Group Summing [message #143423 is a reply to message #143201] Thu, 20 October 2005 07:31
 raajes_n Messages: 33Registered: April 2005 Location: India Member
Sorry
Mr. Joy Division
your query is too simple & works fine, but It uses rownum
my actual aim is to get the result without using
analytic functions or Rownum or rowid

I must thank U a lot for taking effort on me

with regards
rajesh n

Re: Finding Maximum in Group Summing [message #143440 is a reply to message #143423] Thu, 20 October 2005 08:20
 joy_division Messages: 4758Registered: February 2005 Location: East Coast USA Senior Member
Oh, ok, sorry. I didn't know you also didn't want ROWNUM used.
Re: Finding Maximum in Group Summing [message #143702 is a reply to message #142388] Fri, 21 October 2005 09:52
 Martin Eysackers Messages: 80Registered: October 2005 Location: Belgium Member
I had some fun trying this one
so here is my solution

select sno, totalv
from
(
select sno, sum(value) as totalv
from test1
group by sno
)
where totalv = (select max(totalv)
from (select sno, sum(value) as totalv
from test1
group by sno
)
)

this query might return more than one row
if you have to groups (of sno) that have the same sum
Re: Finding Maximum in Group Summing [message #201257 is a reply to message #143702] Fri, 03 November 2006 05:39
 Martin Eysackers Messages: 80Registered: October 2005 Location: Belgium Member
let's try this again

select sno, sum(value)
from test1
group by sno
having sum(value) = (select max(sum(value))
from test1
group by sno)

will also yield the correct result, the trick is in the subquery
which uses max(sum(value))
you can nest a max of 2 aggregate functions
Re: Finding Maximum in Group Summing [message #201314 is a reply to message #201257] Fri, 03 November 2006 08:16
 joy_division Messages: 4758Registered: February 2005 Location: East Coast USA Senior Member
Have you been working on this one problem for over a year?
Re: Finding Maximum in Group Summing [message #201317 is a reply to message #201314] Fri, 03 November 2006 08:35
 Frank Messages: 7880Registered: March 2000 Senior Member
LOL
thinking and rethinking this and finally had his Eureka.
"Oh my, look at the time. Another year has passed.."
Re: Finding Maximum in Group Summing [message #201334 is a reply to message #142388] Fri, 03 November 2006 09:40
 Martin Eysackers Messages: 80Registered: October 2005 Location: Belgium Member
hilarious,
no I am preparing for OCA SQL exams and was looking over some things when I came across this one
it took me 5 minutes, not a year
Re: Finding Maximum in Group Summing [message #201361 is a reply to message #201334] Fri, 03 November 2006 13:20
 Littlefoot Messages: 21127Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
Cool. Keep digging. There must be another question somewhere, older than (just) a year
Re: Finding Maximum in Group Summing [message #201709 is a reply to message #142613] Mon, 06 November 2006 07:58
 aketi Messages: 26Registered: October 2005 Junior Member
--Answer2
select sno,sum(Value)
from (select 1 as sno,10 as Value from dual
union select 2,110 from dual
union select 2,5 from dual
union select 3,5 from dual
union select 3,8 from dual)
group by sno
having sum(Value) = (select max(sum(Value))
from (select 1 as sno,10 as Value from dual
union select 2,110 from dual
union select 2,5 from dual
union select 3,5 from dual
union select 3,8 from dual)
group by sno);

[Updated on: Mon, 06 November 2006 07:59]

Report message to a moderator

Re: Finding Maximum in Group Summing [message #202101 is a reply to message #142388] Wed, 08 November 2006 03:38
 Martin Eysackers Messages: 80Registered: October 2005 Location: Belgium Member
I am sorry Aketi but your answer is just too static
if i add a record in the table then what ?
Re: Finding Maximum in Group Summing [message #202596 is a reply to message #142388] Fri, 10 November 2006 08:41
 rameshuddaraju Messages: 69Registered: June 2005 Location: India Member
I didn't test it but it may be work.

SELECT SNo, SUM(VALUE)
FROM Table1
GROUP BY SNo
HAVING SUM(VALUE) = ( SELECT MAX(SUM(VALUE))
FROM Table1
GROUP BY SNo)

Re: Finding Maximum in Group Summing [message #202648 is a reply to message #202596] Fri, 10 November 2006 12:18
 Frank Messages: 7880Registered: March 2000 Senior Member
you didn't test it, but just copy-paste it from the 7th previous reply?
Re: Finding Maximum in Group Summing [message #202669 is a reply to message #202648] Fri, 10 November 2006 18:15
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Senior Member
I missed this last year, can I have a go?

```select max(sno) keep (dense_rank first order by sum(value)), max(sum(value))
from table1
group by sno```

Doesn't use analytic funcs or rowid, but KEEP only works on 9.0+

Ross Leishman
Re: Finding Maximum in Group Summing [message #203204 is a reply to message #142388] Tue, 14 November 2006 05:41
 Martin Eysackers Messages: 80Registered: October 2005 Location: Belgium Member
you're solutino is ok, except for one thing it should be :

select max(sno) keep (dense_rank last order by sum(value)), max(sum(value))
from table1
group by sno

Re: Finding Maximum in Group Summing [message #216504 is a reply to message #143178] Sun, 28 January 2007 23:34
 pavuluri Messages: 247Registered: January 2007 Senior Member
select * from (select sum(sal) as sal,deptno from emp group by deptno order by sal desc) where rownum=1
Thanks
Srinivas
Re: Finding Maximum in Group Summing [message #216569 is a reply to message #216504] Mon, 29 January 2007 07:56
 joy_division Messages: 4758Registered: February 2005 Location: East Coast USA Senior Member
And how is this answer different from the one I gave 107 days ago? And why do we need to keep giving solutions as it has been answered multiple times already.
 Previous Topic: SP2-0611: Error enabling STATISTICS report Next Topic: How to use % and _ in like function of where clause
Goto Forum:

Current Time: Sat Jul 22 02:29:34 CDT 2017

Total time taken to generate the page: 0.25276 seconds