Top Values w/ Other

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Myra - 10 Jul 2006 22:05 GMT

I want to pull the top 5 Values and then group all other values under an
"Other" category. Can this be done?

Reply to this Message

KARL DEWEY - 10 Jul 2006 22:25 GMT

Give an example of your data and expected results.

> I want to pull the top 5 Values and then group all other values under an
> "Other" category. Can this be done?

Reply to this Message

Myra - 10 Jul 2006 22:38 GMT

I have a group query which is counting total exceptions by category (see
below). I want to add one extra item under category which will be "Other"
which will count the remaining existing categories. I hope this is clear
enough.

Category Total
Item 1 100
Item 2 99
Item 3 98
Item 4 97
Item 5 96
Other 78

> Give an example of your data and expected results.
>
> > I want to pull the top 5 Values and then group all other values under an
> > "Other" category. Can this be done?

Reply to this Message

Ofer Cohen - 10 Jul 2006 22:37 GMT

If for example if I want to display the last 5 categories that were inserted
by date ith there amount, and then display the rest without the Top 5, Can be
done with three queries

1. To select the Top 5
Select Top 5 [category], Amount From TableName Order By [DateField] Desc

2. Group on all records, without the Top 5
SELECT "Other" AS Othercategory, Sum(TableName.Amount) AS SumAmount FROM
TableName
WHERE DateField Not In (Select Top 5 [DateField] From TableName Order By
[DateField] Desc)
GROUP BY "Other"

3. Union query To Join both queries
Select [category], Amount From Query1
Union
Select Othercategory, SumAmount From Query2

Signature

Good Luck
BS"D

> I want to pull the top 5 Values and then group all other values under an
> "Other" category. Can this be done?

Reply to this Message

Ofer Cohen - 10 Jul 2006 22:49 GMT

To use you example

1. To select the Top 5
Select Top 5 [category], Total From TableName Order By [Total] Desc

2. Group on all records, without the Top 5
SELECT "Other" AS Othercategory, Sum(Total) AS SumTotal FROM
TableName
WHERE DateField Not In (Select Top 5 [Total] From TableName Order By
[Total] Desc)
GROUP BY "Other"

3. Union query To Join both queries
Select [category], Total From Query1
Union
Select Othercategory, SumTotal From Query2

Signature

Good Luck
BS"D

> If for example if I want to display the last 5 categories that were inserted
> by date ith there amount, and then display the rest without the Top 5, Can be
[quoted text clipped - 17 lines]
> > I want to pull the top 5 Values and then group all other values under an
> > "Other" category. Can this be done?

Reply to this Message