Decomposing a GROUP BY statement

Assuming I have a table SomeTable with the following data:

Primary Key    Column1    Column2    Column3    Column4    Column5    Num
    1           dat1       abc1       dat3       dat4       por7       1
    2           dat1       gcd4       dat3       dat4       yrt8       6
    3           dat1       iut7       dat3       dat4       asd6       2  
    4          other1     other2     other3     other4     other5      4 

Another table SomeTableGrouped with a "Group Byed" version created using a query like this:

INSERT INTO SomeTableGrouped
SELECT Column1, Column3, Column4, SUM(Num)
FROM SomeTable
GROUP BY Column1, Column3, Column4

Primary Key    Column1    Column3    Column4    Num
    100         dat1       dat3       dat4       9
    200        other1     other3     other4      4 

What I'd like to be able to do is, if I have a primary key of SomeTableGrouped, I need to be able to tell which specific rows from SomeTable it came from.

Example:

In a separate table RandomTable, I have data like this:

Primary Key    Column1    SomeTableGroupedId
    1           dat1             100
    2           dat2             100

If I look at the first row, I need to be able to list out开发者_开发问答 row 1 - 3 from SomeTable

How can I do this? I can't change the schema very much (ie. I can only add new columns, remove columns, add a new table) so a dirty solution is perfectly fine with me.

I think this is what you want.

SELECT id
FROM SomeTable
INNER JOIN SomeTableGrouped ON
    (SomeTable.Column1 = SomeTableGrouped.Column1) AND
    (SomeTable.Column2 = SomeTableGrouped.Column2) AND
    (SomeTable.Column3 = SomeTableGrouped.Column3) 
WHERE SomeTableGrouped.id = ...

You don't even need to create all those tables, you only need SomeTable. But here we go...

If you want to find the IDs of the records that summed up, just relate them as they were created:

select st.PrimaryKey as STPK,  stg.PrimaryKey as STGPK
from SomeTable st 
inner join SomeTableGrouped stg
on (st.Column1 = stg.Column1 and 
    st.Column3 = stg.Column3 and 
    st.Column5 = stg.Column5)

However, you should not even have created SomeTableGroupedas a table. It could be a view (look here to see how create views in DB2).

That way, you make sure data is always up-to-date and you don't have to worry about back tracking ("what if Num gets updated?").