Home » SQL & PL/SQL » SQL & PL/SQL » Finding Maximum in Group Summing
Finding Maximum in Group Summing [message #142388] Fri, 14 October 2005 07:54 Go to next message
raajes_n
Messages: 33
Registered: 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 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: 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 Go to previous messageGo to next message
aketi
Messages: 26
Registered: October 2005
Junior Member
--Answer1 Cool
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 Cool
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 Go to previous messageGo to next message
raajes_n
Messages: 33
Registered: 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 Go to previous messageGo to next message
Rahul Desai
Messages: 26
Registered: 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
--------------------------------------------


Smile
Rahul Desai
Re: Finding Maximum in Group Summing [message #143178 is a reply to message #142939] Wed, 19 October 2005 07:07 Go to previous messageGo to next message
raajes_n
Messages: 33
Registered: 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 Go to previous messageGo to next message
kojakhu
Messages: 6
Registered: 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 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: 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 Go to previous messageGo to next message
raajes_n
Messages: 33
Registered: 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 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: 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 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: 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 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
Member
let's try this again Smile

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 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: 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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
LOL
thinking and rethinking this and finally had his Eureka.
"Oh my, look at the time. Another year has passed.."
icon1.gif  Re: Finding Maximum in Group Summing [message #201334 is a reply to message #142388] Fri, 03 November 2006 09:40 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: 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 Razz
it took me 5 minutes, not a year Smile
Re: Finding Maximum in Group Summing [message #201361 is a reply to message #201334] Fri, 03 November 2006 13:20 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Cool. Keep digging. There must be another question somewhere, older than (just) a year ./fa/450/0/
Re: Finding Maximum in Group Summing [message #201709 is a reply to message #142613] Mon, 06 November 2006 07:58 Go to previous messageGo to next message
aketi
Messages: 26
Registered: October 2005
Junior Member
--Answer2 Cool
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 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: 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 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: 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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: 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 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: 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 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: 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


Mad
Re: Finding Maximum in Group Summing [message #216504 is a reply to message #143178] Sun, 28 January 2007 23:34 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: 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 Go to previous message
joy_division
Messages: 4640
Registered: 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: Mon Dec 05 09:11:50 CST 2016

Total time taken to generate the page: 0.15543 seconds