Top 12 Sum Problem

Forum Moderators: open

Message Too Old, No Replies

Taking the highest 12 values and adding them together?

Jhet

8:45 pm on Jun 19, 2008 (gmt 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?

LifeinAsia

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

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]

Jhet

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

I'll give that a try. Thank you!

plumsauce

5:05 am on Jun 20, 2008 (gmt 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.

LifeinAsia

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

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