Is there a way to do something like SQL NOT top statement?

Question

I'm trying to make a SQL statement that gives me the top X records and then all sums all the others. The first part is easy...

select top 3 Department, Sum(sales) as TotalSales
from Sales
group by Department

What would be nice is if I union a second query something like...

select NOT top 3 "Others" as Department, Sum(sales) as TotalSales
from Sales
group by Department

... for a result set that looks like,

Department    TotalSales
-----------   -----------
Mens Clothes  120.00
Jewelry       113.00
Shoes          98.00
Others        312.00

Is there a way to do an equivalent to a NOT operator on a TOP? (I know I can probably make a temp table of the top X and work with that, but I'd prefer a solution that was just a single sql statement.)

Answer 1

WITH    q AS
        (
        SELECT  ROW_NUMBER() OVER (ORDER BY SUM(sales) DESC) rn,
                CASE
                WHEN ROW_NUMBER() OVER (ORDER BY SUM(sales) DESC) <= 3 THEN
                        department
                ELSE
                        'Others'
                END AS dept,
                SUM(sales) AS sales
        FROM    sales
        GROUP BY
                department
        )
SELECT  dept, SUM(sales)
FROM    q
GROUP BY
        dept
ORDER BY
        MAX(rn)