Sum total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum total

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-01-31 : 02:19:18

I have a list of items to count, I only want to select top 5 decending, while the balance will be sum up.

Item Total
Bottle 10
Pen 8
Paper 7
Book 5
Eraser 4

Pencil 3
Ruler 3
Red Pen 1

Should it be like this?

SELECT top 5 item, total from tableA

How can I sum up the balance to be named as 'Others'?

Item Total
Bottle 10
Pen 8
Paper 7
Book 5
Eraser 4
Others 7

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-01-31 : 03:55:09

Hmm..Anybody?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-01-31 : 04:03:38

create table #t1(Item varchar(10), Total float)

insert into #t1
select 'Bottle', 10
union
select 'Pen', 8
union
select 'Paper', 7
union
select 'Book', 5
union
select 'Eraser', 4
union
select 'Pencil', 3
union
select 'Ruler', 3
union
select 'Red Pen', 1

select * from #t1

select Top 5 * into #t2 from #t1 order by Total desc

select * from #t2
union all
select 'Others', SUM(Total) from #t1
where Item not in (select Item from #t2)

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources