Finding the person who spent the most money

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 Programming

Finding the person who spent the most money

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-03-16 : 09:34:25

Hi Guys,

So I am trying to find the person who spent the most money in a table.
Now I did the following 2 queries to find the results


select distinct cardno
       ,sum(spend)TotalSpend into EachCustomerTotalSpend
from transactions


select cardno
       ,max(TotalSpend )
from EachCustomerTotalSpend

The above code actually worked but I wanted to make it one query, does anyone have any ideas.

Thanks much

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 10:02:03

This is to find the cardno which has most amount spent on it,

select top 1 cardno,sum(spend)
from transactions
group by cardno
order by sum(spend) desc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 10:05:14

quote:
The above code actually worked ...

Not sure how that worked since you don't have a group by clause anywhere there. Even if you had it, your first query and second query will fetch you the same result. It'll list the total sum against all card numbers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 10:17:22

if sql 2005


select top 1 cardno,... other columns
from
(
select *,sum(spend) over (partition by cardno) as cardsum
from transactions
)t
order by cardsum desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources