G
Guest
I want to pull the top 5 Values and then group all other values under an
"Other" category. Can this be done?
"Other" category. Can this be done?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Give an example of your data and expected results.
G
Guest
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
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
G
Guest
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
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
G
Guest
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
--
Good Luck
BS"D
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
--
Good Luck
BS"D
Ofer Cohen said: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
--
Good Luck
BS"D
Myra said:I want to pull the top 5 Values and then group all other values under an
"Other" category. Can this be done?