SQLTeam.com | Weblogs | Forums

Sql top list

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

Can someone help me achieve this.
Many Thanks

Kind Regards
Apy

select top 10 name,
amount from table
order by amount desc

Many Thanks :slight_smile:
It does return Top Name but How to get the
Others 1308 i.e. ( 7044 - Top 10)
Sum 7044

select name, amount from table
except
select top 10 name, amount from table order by amount desc

or

select name, amount from table
order by amount desc
offset 10 rows

Hi

I was able to do it like this
Please correct me if i am wrong
Thanks

create data script

> use [AdventureWorks2012]
> go 
> 
> DROP TABLE mytbl;
> 
> CREATE TABLE mytbl 
>   ( 
>      Name varchar(100) null,
>      Amount int NULL
>   ); 
> 
> INSERT INTO mytbl 
> VALUES 
> ('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)

Query to get results

> ; 
> 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 

1 Like

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

Could you please update the above code with what you meant. Actually I am new with SQL and difficult to understand for me.
Many thanks for your time.

I appreciate and very thankful for your effort to solve my problem.