Report Groups and No groups

[CoginAustin]

I have a table:

"ID" "Name"  "Qty"
----- ---------- --------
  1     Red         5
  2     Blue        10
  3     White      15
  4     Black       20

I want a report that will show the following:

Red - 5
Black - 20
White, Blue - 25

Note: Some are grouped together and some are not. The groupings can change at any time. I also may need to group 3 or more rows together to get a total qty.

What is the best way to accomplish this grouped and not grouped values?

[Draoued]

How is the grouping defined ??

I would create a grouping table, so if the grouping is changing only this table is updated and the report will reflect the changes.

Grouping table:
GroupID       ID      Desc
      1            1        Red
      2            4        Black
      3            2        White,Blue
      4            3        White,Blue


select Grouping.Desc , sum(table.Qty) from Grouping, table
where Grouping.ID = table.ID

table is the table described in your post.

[Darek]

You could use last(Qty) order by ID

[sir_jeroen]

What about creating a custom grouping?

ID" "Name"  "Qty"
----- ---------- --------
  1     Red         5
  2     Blue        10
  3     White      15
  4     Black       20

Solve it like:
add a calculation eg NEW_NAME:
Case when (name = 'Blue' or name = 'White' ) then ( 'Blue/white' ) else (Name)  end

and group on NEW_NAME,sum (QTY)

that's all