SIMPLE Group By/SUM Query - 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 Programming

SIMPLE Group By/SUM Query

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-08-02 : 15:32:10

Hi All - I have the following table
[CODE]
REP SALES LOCATION
John 20 NewYork
John 50 Chicago
Alex 40 Boston
Alex 50 LosAngeles
Adam 20 Wichita
Adam 10 Miami
[/CODE]
I would like it to output the rep and their sales just ONCE, based on the highest sales for that city. For example, it would output the following:
[CODE]
REP SALES LOCATION
John 50 Chicago
Alex 50 LosAngeles
Adam 20 Wichita
[/CODE]
I thought this can be acheived through a simple GROUP BY statement, but I'm doing it wrong:

[CODE]
SELECT REP, SALES, LOCATION
FROM SALES_TABLE
GROUP BY REP, SALES, LOCATION
HAVING MAX(SALES)

[/CODE]
Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-02 : 15:42:20

You can use ROW_NUMBER() function to do this like this:

SELECT
	REP, SALES, LOCATION
FROM
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY REP ORDER BY Sales DESC) AS RN
	FROM
		SalesTable
) s
WHERE RN = 1;

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-08-02 : 15:51:15

Worked, Thanks!!!!!!!!!!!!!!!!!!! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 16:07:56

[code]
SELECT Rep,Sales,Location
FROM (SELECT *,MAX(Sales) OVER (PARTITION BY Rep) AS MaxSales
FROM table
)t
WHERE Sales=MaxSales
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources