select top 5

[AJ]

Is there a way to select top 5 (stores) based on a calculation?
example

select * from stores
where ?? in (select top 5 avg(sales + amount))

 

[Allen Browne]

The subquery would need to read from your sales table, and group by the
foeign key.

Perhaps something like this:

select * from stores
where StoreID in
(select top 5 StoreID
FROM Sales
GROUP BY StoreID, avg(sales + amount)
ORDER BY avg(sales + amount) DESC);

More info on subqueries:
http://allenbrowne.com/subquery-01.html