Sum and Top function - 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

Sum and Top function

Author

Topic

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-09-08 : 12:37:34

If I run select top 1 orders * from 'table' I get d returned. What I need is the sum of orders and have customer a returned with 12. Ideas? Thanks

'TABLE'
Customer Orders
a 3
b 4
c 6
d 10
c 3
b 4
a 9

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-08 : 12:43:48

SELECT Customer, [Orders] = SUM(Order)
FROM yourTable
GROUP BY Customer
HAVING SUM(Orders) = 12

Jim

Everyday I learn something that somebody else already knew

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-09-08 : 12:46:43

In the example I gave I know what the top 1 result would be, but I'll be applying the query to a table that has thousands of rows and I'll have no idea which the top customer will be.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-08 : 12:59:18

SELECT TOP 1 Customer ,Premium
FROM
(select customer,[premium] = sum(premium) from yourtable group by customer)
ORDER BY Premium DESC

Jim

This doesn't take into account ties. Are you using SQL 2005?

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources