Top 20 then "Others" - 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

Top 20 then "Others"

Author

Topic

smithysrat
Starting Member

3 Posts

Posted - 2007-10-02 : 08:22:16

I need to produce a query which show the top 20 results individually and then groups the remainder into a single "Others" result.

The query as it stands is:

SELECT distinct
geographical.fmscity.f_aptname,
sum(itinerary.Total_Cost) as totcost
FROM
bform.bform
inner Join bclients.department ON bform.bform.REF_department = bclients.department.ID_uniq
inner Join bclients.department AS org ON bclients.department.REF_organisation = org.ID_uniq
inner Join bform.event_log ON bform.bform.ID_uniq = bform.event_log.REF_bform
inner Join bform.itinerary ON bform.bform.ID_uniq = bform.itinerary.REF_Bform
inner join bform.passenger on right(left(itinerary.PAX_REFS,8),6) = passenger.ID_uniq
inner join misc_data.airlines on right(left(itinerary.Journey_Data,33),2) = misc_data.airlines.code
left join geographical.fmscity on itinerary.Des_City = geographical.fmscity.f_aptcode
where
org.ID_uniq = '231' AND
bform.event_log.`Type` = 'inv' AND
bform.event_log.`Date` BETWEEN '2007-09-24' AND '2007-09-28' AND
bform.bform.Booking_Status = 'g'
group by geographical.fmscity.f_aptname
order by totcost desc

Is there an easy way to do this?

Thanks Muchly!

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 08:30:22

"Is there an easy way to do this?"

UNION ALL perhaps?

SELECT TOP 20 ... FROM ...
UNION ALL
SELECT Summary stuff about the remainder

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-02 : 08:32:28

aa but don't forget the ordering part!
select * from
(
SELECT TOP 20 1 as Ordering, ... FROM ...
UNION ALL
SELECT 2 as Ordering, Summary stuff about the remainder
) t
order by Ordering

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 08:39:25

Won't UNION ALL delivery the second part "last" in all instances?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-02 : 09:01:12

don't think so since it's a resultset and resultsets aren't ordered by default.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

smithysrat
Starting Member

3 Posts

Posted - 2007-10-02 : 09:06:55

Thanks, I'll give it a go!

Thanks Muchly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources