max of sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

max of sum

Author

Topic

neelima83
Starting Member

1 Post

Posted - 2011-10-16 : 19:18:00

hi
i have tables
client:
clientno
cname
phone

purchase:
purchaseno
recptno
amount
clientno

have to write a query to find who is the top client - one who has purchased the most ie., the one with highest total purchase amount among all company's clients and should display clientno, cname and the total purchase amount by that client
so i suppose it is max of sum (amount)
can anyone please help me to do it
this is what iam thinking
select clientno, cname, sum(amount)
from client c, purchase p
and sum(amount)>= ALL
(select sum(amount)
from purchase
group by clientno);

then i get this error
sum(amount) >= all (select sum(amount)
*
ERROR at line 4:
ORA-00934: group function is not allowed here

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-16 : 19:52:36

[code]
select top 1 clientno, cname, sum(amount)
from client c, purchase p
and sum(amount)>= ALL
(select sum(amount)
from purchase
group by clientno)
order by sum(amount) desc[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqlmaster555112
Starting Member

13 Posts

Posted - 2011-10-18 : 11:47:09

select IIF(sum=Max(Sum),Clientno,0),max(sum) as [Max purchase] from (select clientno, cname, sum(amount) as Sum
from client c, purchase p
and sum(amount)>= ALL
(select sum(amount)
from purchase
group by clientno))
May do the trick thanks plz reply..........

@sqlmaster

http://www.intelcs.com/SQL_Server_Consultant.aspx

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-10-19 : 05:29:46

IIF can only be used with SQL Server 2012 (code name: Denali)

--------------------------
http://connectsql.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 06:15:49

quote:
Originally posted by lionofdezert

IIF can only be used with SQL Server 2012 (code name: Denali)

--------------------------
http://connectsql.com/

yep thats true

the equivalent expression you can use in earlier version is CASE WHEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources