Show Top 10 and sum the rest?

[Bish]

I know how to get a query to show the Top 10 of a certain of a certain
category. But is there a way to show the Top 10 and then have a sum of the
rest.

For example, if I want to see my Top 10 Customers by how much I've sold them
and then have a sum of the sales to all other customers, is there a way to do
this? Thanks.

 

[Brendan Reynolds]

I know how to get a query to show the Top 10 of a certain of a certain
category. But is there a way to show the Top 10 and then have a sum of
the
rest.

For example, if I want to see my Top 10 Customers by how much I've sold
them
and then have a sum of the sales to all other customers, is there a way to
do
this? Thanks.

Using the Orders table from Northwind for illustration ...

This query will select the top 10 ...

SELECT TOP 10 Orders.[Shipping Fee], Orders.[Order ID]
FROM Orders
ORDER BY Orders.[Shipping Fee] DESC , Orders.[Order ID] DESC;

This query will sum the records not in the top 10 ...

SELECT Sum(Orders.[Shipping Fee]) AS [SumOfShipping Fee]
FROM Orders
WHERE (((Orders.[Order ID]) Not In (SELECT [Order ID] FROM Query1)));

And this query will combine the two ...

SELECT [Shipping Fee], [Order ID]
FROM Query1
UNION SELECT [SumOfShipping Fee], 0
FROM Query2

 

[Bish]

Great thanks... just one more thing. For me the sum of the others shows up
as the 4th most in the Union Query. If I want to have them displayed in
descending order, execpt I want that "Other" group last no matter what, is
there a way to do that?

I know how to get a query to show the Top 10 of a certain of a certain
category. But is there a way to show the Top 10 and then have a sum of
the
rest.

For example, if I want to see my Top 10 Customers by how much I've sold
them
and then have a sum of the sales to all other customers, is there a way to
do
this? Thanks.

Using the Orders table from Northwind for illustration ...

This query will select the top 10 ...

SELECT TOP 10 Orders.[Shipping Fee], Orders.[Order ID]
FROM Orders
ORDER BY Orders.[Shipping Fee] DESC , Orders.[Order ID] DESC;

This query will sum the records not in the top 10 ...

SELECT Sum(Orders.[Shipping Fee]) AS [SumOfShipping Fee]
FROM Orders
WHERE (((Orders.[Order ID]) Not In (SELECT [Order ID] FROM Query1)));

And this query will combine the two ...

SELECT [Shipping Fee], [Order ID]
FROM Query1
UNION SELECT [SumOfShipping Fee], 0
FROM Query2

 

[Brendan Reynolds]

You could add a calculated column to the union query to sort by ...

SELECT [Shipping Fee], [Order ID], 1 as SortCol
FROM Query1
UNION SELECT [SumOfShipping Fee], 0, 2
FROM Query2
ORDER BY SortCol, [Shipping Fee]

--
Brendan Reynolds

Great thanks... just one more thing. For me the sum of the others shows
up
as the 4th most in the Union Query. If I want to have them displayed in
descending order, execpt I want that "Other" group last no matter what, is
there a way to do that?

I know how to get a query to show the Top 10 of a certain of a certain
category. But is there a way to show the Top 10 and then have a sum of
the
rest.

For example, if I want to see my Top 10 Customers by how much I've sold
them
and then have a sum of the sales to all other customers, is there a way
to
do
this? Thanks.

Using the Orders table from Northwind for illustration ...

This query will select the top 10 ...

SELECT TOP 10 Orders.[Shipping Fee], Orders.[Order ID]
FROM Orders
ORDER BY Orders.[Shipping Fee] DESC , Orders.[Order ID] DESC;

This query will sum the records not in the top 10 ...

SELECT Sum(Orders.[Shipping Fee]) AS [SumOfShipping Fee]
FROM Orders
WHERE (((Orders.[Order ID]) Not In (SELECT [Order ID] FROM Query1)));

And this query will combine the two ...

SELECT [Shipping Fee], [Order ID]
FROM Query1
UNION SELECT [SumOfShipping Fee], 0
FROM Query2