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.