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 Programming

Max of sum

Author

Topic

vishalj88
Starting Member

1 Post

Posted - 2013-10-09 : 17:48:55

Hi Developers,

I'm new to sql. I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.

Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200

Report:

Cust ID Year Sales
C1 2000 250
C3 2001 200

Please provide your input.

Thanks

Vishal

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-09 : 17:55:30

[code]SELECT CustId, YEAR, Sales FROM
(
SELECT
custId, YEAR,SUM(sales) Sales,
ROW_NUMBER() OVER (PARTITION BY year ORDER BY SUM(sales) DESC) AS RN
FROM
YourTable
GROUP BY
custid, YEAR
) s WHERE RN = 1;[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources