SQL: how to display top 5 then sum the rest

[sweetpotatop]

Hello,

I would like to query the top 5 best companies' sales (total sales),
then total the rest, what is the quickest and effective SQL to query
it?


Thanks in advance

 

[Tom Ellison]

Dear Sweet:

Start with a query that identifies the top 5. Something like:

SELECT TOP 5 CompanyName
FROM YourTableNameGoesHere
ORDER BY SUM(SalesAmount) DESC

Then, using this, get the top 5 summed:

SELECT TOP 5 CompanyName, SUM(SalesAmount) AS TotalSales
FROM YourTableNameGoesHere
ORDER BY SUM(SalesAmount) DESC

Now a query for the sum of the rest:

SELECT "The rest" AS CompanyName, SUM(SalesAmount) AS TotalSales
FROM YourTableNameGoesHere
WHERE CompanyName NOT IN (
SELECT TOP 5 CompanyName
FROM YourTableNameGoesHere
ORDER BY SUM(SalesAmount) DESC)

A UNION ALL of these finishes it:

SELECT TOP 5 CompanyName, SUM(SalesAmount) AS TotalSales
FROM YourTableNameGoesHere
ORDER BY SUM(SalesAmount) DESC
UNION ALL
SELECT "The rest" AS CompanyName, SUM(SalesAmount) AS TotalSales
FROM YourTableNameGoesHere
WHERE CompanyName NOT IN (
SELECT TOP 5 CompanyName
FROM YourTableNameGoesHere
ORDER BY SUM(SalesAmount) DESC)

How does this do? Put in your actual table and column names.

Tom Ellison