Home » SQL & PL/SQL » SQL & PL/SQL » Max on Sum
Max on Sum [message #636223] Mon, 20 April 2015 08:03 Go to next message
zoe2003
Messages: 2
Registered: April 2015
Junior Member
Hi,
I have this data:

client	creditCard	amount
1	A		10
1	A		20
1	B		40
1	C		5	
1	C		10
1	C		20
2	A		40
2	D		60


I need to write a query (Oracle) that will return the clientID and the creditCard type with the highest spending :
In this case:

client	creditCard 
1	B
2	D


Tnx in advance.
z


[mod-edit: code tags added by bb]

[Updated on: Fri, 08 May 2015 07:31] by Moderator

Report message to a moderator

Re: Max on Sum [message #636225 is a reply to message #636223] Mon, 20 April 2015 08:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: Max on Sum [message #636226 is a reply to message #636223] Mon, 20 April 2015 08:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
zoe2003 wrote on Mon, 20 April 2015 18:33

I need to write a query (Oracle) that will return the clientID and the creditCard type with the highest spending


So what's the problem in using MAX? In a sub-query, find maximum amount grouped by client. Alternatively, you could also use analytic ROW_NUMBER(). What have you tried so far?
Re: Max on Sum [message #636890 is a reply to message #636226] Tue, 05 May 2015 03:41 Go to previous messageGo to next message
browncat
Messages: 9
Registered: May 2015
Junior Member
The query is....
SELECT client, creditCard, MAX(amount) FROM <Table-name> GROUP BY client;

[Updated on: Tue, 05 May 2015 03:45]

Report message to a moderator

Re: Max on Sum [message #636891 is a reply to message #636890] Tue, 05 May 2015 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@browncat,

Welcome to the forum, it is nice to see you want to help but test your solutions before posting them.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

[Updated on: Tue, 05 May 2015 03:51]

Report message to a moderator

Re: Max on Sum [message #636892 is a reply to message #636890] Tue, 05 May 2015 03:50 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
SQL> select client, credit_card, max(amount)
  2  from t1
  3  group by client;
select client, credit_card, max(amount)
               *
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Re: Max on Sum [message #636893 is a reply to message #636890] Tue, 05 May 2015 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
No it's not. That would give ORA-00979.
If you fix that it'll give more rows than the OP wants.
Re: Max on Sum [message #637046 is a reply to message #636893] Fri, 08 May 2015 05:23 Go to previous messageGo to next message
saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai
Junior Member
this will help you

select client, creditCard
  from (select client,
               creditCard,
               dense_rank() over(partition by client order by amount desc) ranking
          from (select client,
                       creditCard,
                       max(amount) amount
                  from prc_table_1
                 group by creditCard, client))
 where ranking = 1;


Tnx,
Sai Pavan

[Updated on: Fri, 08 May 2015 05:54]

Report message to a moderator

Re: Max on Sum [message #637051 is a reply to message #637046] Fri, 08 May 2015 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The inner most inline view is useless, you can simply the query.

Re: Max on Sum [message #640230 is a reply to message #637051] Fri, 24 July 2015 04:40 Go to previous messageGo to next message
nqtrung
Messages: 25
Registered: April 2007
Junior Member
select * from t1
where amount in (select max(amount) from t1 group by client);


Good luck
Re: Max on Sum [message #640232 is a reply to message #640230] Fri, 24 July 2015 04:57 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Nope - that works with OPs example data but won't in general.
What happens if you add this row:
2	A		30


Plus you do realise this thread is 2 months old. You'd like to hope the OP had solved their issue by now.
Re: Max on Sum [message #640234 is a reply to message #640232] Fri, 24 July 2015 05:05 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
You'd hope that they'd have had the courtesy to let us know too Wink
Re: Max on Sum [message #640418 is a reply to message #640234] Tue, 28 July 2015 06:05 Go to previous messageGo to next message
prabhu.MS
Messages: 1
Registered: July 2015
Junior Member

Hi,the below query works...

SELECT CLIENT,
CREDITCARD
FROM <Table_name>
WHERE (client,amount) IN
( SELECT DISTINCT CLIENT,MAX(AMOUNT) FROM <Table_name> GROUP BY CLIENT
);


Re: Max on Sum [message #640419 is a reply to message #640418] Tue, 28 July 2015 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DISTINCT is useless, GROUP BY returns the distinct groups.
In addition, when you use IN, DISTINCT is also useless even if the subquery returns several duplicated rows as IN is a set operator and a set has no duplicate (that is Oracle eliminates them).

You should try to do it with RANK function which avoid to scan the table twice.
Re: Max on Sum [message #640422 is a reply to message #640418] Tue, 28 July 2015 06:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi prabhu.MS,

Welcome to the forum!

Thanks for contributing.

Please read and follow the OraFAQ Forum Guide and How to use [code] tags.
Re: Max on Sum [message #640434 is a reply to message #640422] Tue, 28 July 2015 08:54 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
That query also doesn't work in general for the same reason nqtrung's attempt above doesn't work.
Add the extra row of data I suggested and see what you get.
Re: Max on Sum [message #640457 is a reply to message #640434] Tue, 28 July 2015 21:32 Go to previous messageGo to next message
nqtrung
Messages: 25
Registered: April 2007
Junior Member
SELECT CLIENT, CREDITCARD, AMOUNT FROM T1
WHERE (CLIENT, AMOUNT) IN (SELECT CLIENT, MAX(AMOUNT) FROM T1 GROUP BY CLIENT);


Hope it's right Smile
Re: Max on Sum [message #640459 is a reply to message #640457] Tue, 28 July 2015 22:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Hope it's right

I hope you keep trying after SUM(AMOUNT) for each Credit Card
Re: Max on Sum [message #640469 is a reply to message #640457] Wed, 29 July 2015 02:45 Go to previous message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
nqtrung wrote on Wed, 29 July 2015 03:32
SELECT CLIENT, CREDITCARD, AMOUNT FROM T1
WHERE (CLIENT, AMOUNT) IN (SELECT CLIENT, MAX(AMOUNT) FROM T1 GROUP BY CLIENT);


Hope it's right Smile


Oh good grief.
That's just prabhu.MS's attempt with the distinct removed. Of course it's not right, for the same reason I've already pointed out.
You need the client/credit card where the sum(amount) for that client/credit card is the greatest of all the client/credit card sums for each client.
Previous Topic: concatenate the values in the o/p
Next Topic: Joining
Goto Forum:
  


Current Time: Thu Apr 18 17:59:34 CDT 2024