Hi,
I am new with SQL.
I have a table that looks like this
Name Amount
A 100
B 200
C 600
D 800
E 423
F 400
G 261
H 368
I 953
J 324
K 658
L 267
M 156
N 398
O 568
P 568
I would like a result from above table like this (Top 10 customer)
Name Amount
I 953
D 800
K 658
C 600
O 568
P 568
E 423
F 400
N 398
H 368
Others 1308
Sum 7044
> ;
> WITH abc_cte
> AS (SELECT TOP 10 *
> FROM mytbl
> ORDER BY amount DESC)
> SELECT *
> FROM abc_cte
> UNION ALL
> SELECT 'others',
> Sum(amount)
> FROM mytbl
> WHERE NAME NOT IN (SELECT NAME
> FROM abc_cte)
> UNION ALL
> SELECT 'sum',
> Sum(amount)
> FROM mytbl
Looks OK to me. I don;t know if you are guaranteed to get the UNION ALL to sequence in that order? SQL is iffy about repeatable ordering WITHOUT an Order By clause (but I don't, offhand, know if UNION ALL result ordering is repeatable)
If not add a [Section] column and set to 1, 2, 3 (for each section, natch!) and then ORDER BY [Section], Amount DESC