merging two data sets

[deleted_user]

Hi, I'm currently working with two data sets similar to:

#1:
data parts;

part_no price
123 15
234 25
237 20
355 28
789 55

#2:
data sales;

ID Trans_NO Part_NO Quantity
03 1 234 5
03 1 123 9
03 2 237 4
01 1 355 5
01 1 234 3
01 1 123 9
01 2 355 5
02 1 237 11

I want to make a new data set that only shows ID (sorted by ID), the transaction
number, and the total sale for each transaction.

I think I need to first merge the two data sets by Part_no so that I can
compute to total sale, but I'm having trouble with what to do after this to get the proper dataset.
Any help would be much appreciated!
Helena

[LinusH]

I think that a SQL join would more appropriate/easier to do.
You can calculate sales per row, and then summarize per transaction no in one step: (pseudo-code):

create table total_sales as
select id, trans_no, quantity * price as total_sale
from sales left join parts
on sales.part_no eq parts.part_no
group by id, trans_no
;

/Linus