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?
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.
You could use last(Qty) order by ID
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