Sql top list

Transact-SQL
1

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

2

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

3

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

4 
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
5

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

image
image.png424×679 19.4 KB

1 Like
6

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

7

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.

8

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