Select statement question

Question

Select statement question

jroth 34505

Newbie

Points: 6
More actions
January 14, 2010 at 9:51 am

#94412

My dataset has 3 columns: customer id, store location, charges
I'd like to write a single select statment that will show each customer id once, with the store location where they have the most charges, and the sum of the charges for that store location.
I can do this with two statements and a cursor file, but I'd like to do it with a single statement and no cursor file.
Thanks.
joel

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 1

Sounds like a homework assignment, can you post what you have tried so far. Also what happens if a customer has two store locations with the same highest charges?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

jroth 34505 Newbie Points: 6 More actions · Answer 2

Not a homework assignment, just a simplification of what I need to do.

This gives the max amount, but doesn't give the location:

select a.custID, max(new.amt) from smallchg a inner join;

(select custID, location, sum(amount) as amt from smallchg group by custID, location) as new ;

on a.custID= new.CustID group by a.CustID

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 4

You want to lookup Ranking Functions in Books Online and probably use ROW_NUMBER() to get the results you are looking for.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs