sql query for report produce wrong result

I have this problem regarding sql which i will be using in a webservice if I can get this right. What I wanted to do is create a summary report of a transaction. The transaction have header and lines. In a transaction, we are going to input a many planks.

After the inputing, I would like to produce a report that would add all the plank that belongs to a category, then multiply all the planks by the price of that supplier so that I can have the total amount. Here is a pic:

below is my sql which produce wrong output:

select
      t1.transaction_num,
      wo1.wood_classification_desc,
      wo2.wood_specie_desc,
      sum(t2.board_foot) as total_board_foot,
      su1.price,
      sum(t2.board_foot*su1.price) as total_amount
    from
      "transaction_hdr" t1
      left join "transaction_lne" t2 on (t1.transaction_id = t2.transaction_id)
      left join "supplier" su2 on (t1.supplier_id = su2.supplier_id)
      left join "supplier_price" su1 on (t2.price = su1.price)
      left join "wood_classification" wo1 on (t2.wood_classification_id = wo1.wood_classification_id)
      left join "wood_specie" wo2 on (wo1.wood_specie_id = wo2.wood_specie_id)
    group by
      t1.transaction_num,wo1.wood_classification_desc,su1.price,su2.supplier_name,wo2.wood_specie_desc
    order by transaction_num,wo2.wood_specie_desc

Evrytime I run that sql, it produces somthing like this:

the transaction that i test only have five planks. 4 planks under Mahogany 6" wider - 7ft. up and 1 Mahogany 5" wider - 7ft. up.开发者_C百科

I would guess, that in one of the left joins you have more than one record, which means it give you wrong sum (group by will affect the set after the left join).

Just run the sql without the grouping and check what you get.

So it's probably not the multiplication that causes the problem, it's the sum.

Do you have more than one record in supplier_price with price = 13.33 ? That would be my guess, as all the other joins appear to be on primary keys..

EDIT:

Your problem is that you're joining to supplier_price on the price field, which is not a valid key. Given that your output doesn't take anything from the supplier_price table, I'd be inclined to remove it from the query altogether as below:

select
      t1.transaction_num,
      wo1.wood_classification_desc,
      wo2.wood_specie_desc,
      sum(t2.board_foot) as total_board_foot,
      t2.price,
      sum(t2.board_foot*t2.price) as total_amount
    from
      "transaction_hdr" t1
      left join "transaction_lne" t2 on (t1.transaction_id = t2.transaction_id)
      left join "supplier" su2 on (t1.supplier_id = su2.supplier_id)
      left join "wood_classification" wo1 on (t2.wood_classification_id = wo1.wood_classification_id)
      left join "wood_specie" wo2 on (wo1.wood_specie_id = wo2.wood_specie_id)
    group by
      t1.transaction_num,wo1.wood_classification_desc,t2.price,su2.supplier_name,wo2.wood_specie_desc
    order by transaction_num,wo2.wood_specie_desc

This may solve the issue. But it would help if you provided the relationships between the tables (and the tables' primary keys):

select
      t1.transaction_num,
      wo1.wood_classification_desc,
      wo2.wood_specie_desc,
      sum(t2.board_foot)
        as total_board_foot,
      ( SELECT DISTINCT su1.price
        FROM "supplier_price" su1
        WHERE t2.price = su1.price
      ) AS price,
      sum(t2.board_foot) *
          ( SELECT DISTINCT su1.price
            FROM "supplier_price" su1
            WHERE t2.price = su1.price
          )
        as total_amount
    from
      "transaction_hdr" t1
      left join "transaction_lne" t2
        on (t1.transaction_id = t2.transaction_id)
      left join "supplier" su2 
        on (t1.supplier_id = su2.supplier_id)
      left join "wood_classification" wo1
        on (t2.wood_classification_id = wo1.wood_classification_id)
      left join "wood_specie" wo2
        on (wo1.wood_specie_id = wo2.wood_specie_id)
    group by
        t1.transaction_num
      , wo1.wood_classification_desc
      , su2.supplier_name
      , wo2.wood_specie_desc
    order by transaction_num
           , wo2.wood_specie_desc