NewBie Help - Group by

Question

NewBie Help - Group by

Søren

SSChasing Mays

Points: 628
More actions
August 12, 2009 at 5:34 am

#133812

I have a table with 10 customergroups and their sales for one year:
CustomergroupName
Salesfigures
I want to make an SQL-statement that shows the 4 biggest group and then make one extra line (line 5) with a total of the rest. Example:
Denmark 10000
Russia 9999
Poland 8888
Sweden 7777
Rest of World 4000
How can I do that.
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

To get help faster and get working tested code, please read the first article I reference in my signature block regarding asking for assistance.

To really help you, we need the DDL for the table, sample data for the table (in a readily consummable format, and the srticle shows you haw to do that), expected results based on the sample data, and the code you have tried to write so far.

Allister Reid SSCrazy Points: 2666 More actions · Answer 2

USE TEMPDB

DECLARE @temp TABLE (Country VARCHAR(20), Sales INT)

INSERT INTO @temp

SELECT 'Sweden', 7777 UNION ALL

SELECT 'Denmark', 10000 UNION ALL

SELECT 'Russia', 9999 UNION ALL

SELECT 'Country1', 1 UNION ALL

SELECT 'Country2', 1 UNION ALL

SELECT 'Country3', 1 UNION ALL

SELECT 'Poland', 8888 UNION ALL

SELECT 'Country4', 1 UNION ALL

SELECT 'Country5', 1 UNION ALL

SELECT 'Country6', 3995

;WITH RankedCountry(Country, Sales, SalesRank) as

(

SELECT Country, Sales, ROW_NUMBER() OVER(ORDER BY Sales DESC)

FROM @temp

)

SELECT Country, Sales

FROM

(

SELECT Country, Sales, SalesRank

FROM RankedCountry

WHERE SalesRank 4

) T

ORDER BY SalesRank ASC

Allister Reid SSCrazy Points: 2666 More actions · Answer 3

Sorry Lynn, had that posted before I saw your's.

Absolutely agreed, DDLs etc. would be very helpful!