Welcome to WebmasterWorld Guest from 18.206.194.83

Forum Moderators: open

Message Too Old, No Replies

Top 12 Sum Problem

Taking the highest 12 values and adding them together?

     
8:45 pm on Jun 19, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 13, 2006
posts:125
votes: 0


What I'm trying to do is to add up the twelve highest values in a table and return only that result. This works:

Select sum(Price) as P from Table

but this doesnt work:

Select top 12 sum(Price) as P from Table Order by Price

I get this error: "...is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

Does what I'm trying to do make sense?

9:08 pm on June 19, 2008 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5847
votes: 194


Try something like the following:
SELECT SUM(Price) AS P
FROM Table
WHERE SomeID IN (SELECT TOP 12 SomeID, PRICE FROM Table ORDER BY PRICE DESC)

To work, Table will have to have a unique ID (SomeID) to use to identify a specific row. And you may need to tweek the query a bit depoending on the DB you're using.

[edited by: LifeinAsia at 9:10 pm (utc) on June 19, 2008]

10:21 pm on June 19, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 13, 2006
posts:125
votes: 0


I'll give that a try. Thank you!
5:05 am on June 20, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 12, 2003
posts:772
votes: 0



The unique id is not a requirement because the request was for the sum of the 12 highest prices.

so,

select sum(pt.price)
from
(
select top 12 price as price from pricetable order by price descending
)pt

transact-sql syntax.

4:53 pm on June 20, 2008 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5847
votes: 194


Ah, yes, you are correct. Much more elegant.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members