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.

10

hi hope this helps

solved = yes

why am i doing this ? = better t-SQL = using ROLLUP function =sql server 2005

drop create table insert data 
-- Drop the table if it already exists
IF OBJECT_ID('dbo.SalesData', 'U') IS NOT NULL
    DROP TABLE dbo.SalesData;

-- Create the table
CREATE TABLE dbo.SalesData (
    Name CHAR(1) PRIMARY KEY,
    Amount INT
);

-- Insert the provided data
INSERT INTO dbo.SalesData (Name, Amount)
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);

t-sql

;WITH Ranked AS ( SELECT  Name,Amount,ROW_NUMBER() OVER (ORDER BY Amount DESC) AS rn FROM dbo.SalesData )
SELECT 
    ISNULL(GroupName, 'Sum') AS Name,SUM(Amount) AS Amount
FROM 
    ( SELECT CASE WHEN r.rn > 10 THEN 'Others' ELSE r.Name END AS GroupName,r.Amount FROM Ranked r ) AS x
GROUP BY 
     GroupName WITH ROLLUP
ORDER BY 
    CASE WHEN GroupName = 'Others' THEN 1 WHEN GroupName IS NULL THEN 2 ELSE 0 END,  SUM(Amount) DESC;

Result

image
image156×343 3.27 KB