Search code examples
oracleoracle-sqldeveloper

Calculate rate of Columns in Oracle

I have table like below.I also keep a variable in the Oracle package to use in the table below.

For example : q_value = 27;

For each piece column by grouping the table,I want to do this calculation q_value * ((piece_value)/(all_piece_values))

FOR 2111TN IS RATE VALUE:27*/(658)/(658+388))

FOR 2112TN IS RATE VALUE:27*/(388)/(658+388))

PIECE MPIECE PART VALUE
2111TN 2110HB 1 658
2111TN 2110HB 1 658
2112TN 2110HB 2 388
2112TN 2110HB 2 388

so the result of the query is for me ,should be as follows :

PIECE MPIECE PART VALUE RATE
2111TN 2110HB 1 658 17
2112TN 2110HB 2 388 10

How should I create a query?

Solution

  • I don't see any other way here rather than using analytical function

    select piece, mpiece, part, value,
       round(27 * value/(sum(value) over())) rate
  from some_table
 group by piece, mpiece, part, value