Summing Groups

  • Thread starter Thread starter Jaime
  • Start date Start date
J

Jaime

I have the following query that is the result of another query

ID Sales
1 22
2 21
3 11
4 3
5 3
6 2

Is there anyway I can create a query that will give me the following result?
Group Sales
1-3 54
4-6 8

Thank you all for your help!
 
Not without something to groups those ID's together. If all you will ever
have is those 6 ID values then you could create the following saved query
(Call it qryGrouping):

SELECT ID, IIf([ID] In (1,2,3),"A","B") AS Grouping, Quantity
FROM YourTable;

Then the following query would do what you want:

SELECT Grouping, Sum(Quantity) As GroupedSum
FROM qryGrouping
Group By Grouping;

Otherwise, you would need to add a field to your table to group the various
ID's together.
 
Well, there is, but How do you tell which ID belongs to which group? If it is
simply 1,2,3; 4,5,6; 7,8,9;... then you can use a little math on the ID number.

SELECT ((ID-1)\3)*3+1 & "-" & ((ID-1)\3)*3+3 as Group,
Sum(Sales) as Sales
FROM YourQuery
GROUP BY ((ID-1)\3)*3+1 & "-" & ((ID-1)\3)*3+3

That is IF I got all the parentheses in the right place.

Subtract 1 from the ID
INTEGER divide that by 3
Multiply that by 3
Add 1 to get the start of the group
Add 3 to get the end of the group
Concatenate them together with a dash
 
Back
Top