tags:

views:

905

answers:

4

Hi,

I have a co-worker who is working on a table with an 'amount' column. They would like to get the top 5 amounts and the sum of the amounts in the same query.

I know you could do this:

SELECT TOP 5 amount FROM table 
UNION SELECT SUM(amount) FROM table
ORDER BY amount DESC

But this produces results like this:

1000  (sum)
 100
  70
  50
  30
  20

When what they really need is this:

100 | 1000
 70 | 1000
 50 | 1000
 30 | 1000
 20 | 1000

My intuitive attempts to achieve this tend to run into grouping problems, which isn't such an issue when you are selecting a different column, but is when you want to use an aggregate function based on the column you are selecting.

+2  A: 

This might work

SELECT TOP 5 amount, (SELECT SUM(amount) FROM table) 
FROM table 
ORDER BY amount DESC
Vinko Vrsalovic
Perfect thanks (well remove the group by) - I had tried SUM(Select) not SELECT(sum) so I was close but you fixed it thanks :-)
Graphain
Don't know why I added the group by :) Must have been the sleep.
Vinko Vrsalovic
+4  A: 

You can use a CROSS JOIN for this:

SELECT TOP 5 a.amount, b.sum
FROM table a
CROSS JOIN (SELECT SUM(amount) sum FROM table) b
ORDER BY amount DESC
Bob
Thanks this works perfectly too. I didn't think of using the joined table to do the sum, I still tried SUM(b.amount) not b.sum.
Graphain
@Graphain: You should change this to the accepted answer, because the currently accepted answer could have some terrible performance problems. Also, the "CROSS JOIN" identifier may be replaced by a comma, if you wish.
John Gietzen
@John Gietzen - Thanks, I didn't check to see if they optimised equally.
Graphain
+1  A: 

Not really pretty, but this shouls do it:

SELECT TOP 5 amount,  SAmount
FROM table Join 
  (SELECT SUM(amount) As SAmount FROM table)
ORDER BY amount DESC

As said by others, I'd probably use two queries.

IronGoofy
Thanks - I didn't think of the approach of doing the SUM elsewhere and accessing that by a named column.
Graphain
+1  A: 

Another approach using analytic functions (SQL Server 2005+):

SELECT  TOP 5 amount, SUM(amount) OVER()
FROM    table
ORDER BY
        amount DESC
Quassnoi