Update of a field with the result of a select that returns several PL SQL records

Question

Update of a field with the result of a select that returns several PL SQL records

Navigation

#1 by (0 votes)

0

My query is as follows:

I must make a query that adds the prices grouped by several concepts, then, insert those results in the totals column, only when the quantity is different from 0.

It would be like this:

Precio  Departamento    Unidad  Mes Cantidad_item
3              a          1     ene     1
2              a          1     ene     0
12             a          1     feb     1
10             a          1     feb     0
1              a          2     mar     1
8              a          2     mar     0

That is, a select sum precio group by departamento, unidad, mes etc is done.

Up there fantastic. What I do not know is how to get the results of each group to be inserted in the row of totals where the amount of items of each group is different from 0 (when I make the groupings, only one record remains where quantity is different from 0 )

There should be something like this:

Precio  Departamento    Unidad  Mes Cantidad_item   Total
3   a   1   ene 1   5
2   a   1   ene 0   
12  a   1   feb 1   22
10  a   1   feb 0   
1   a   2   mar 1   9
8   a   2   mar 0

sql oracle plsql

asked by merisavino 19.05.2017 в 17:07

source

1 answer

Assign the default value to EditorFor, with current team date Error in CodeIgniter

score 0 · Answer 1

It is not clear what you mean by inserting these results in a column of totals but for the result you expect, I understand that what you want is a selection that groups the information, calculate the total and filter leaving only the records that have amount other than 0.

You will need a query that groups, calculates and also filters on the result of the grouping:

SELECT precio,
       departamento,
       unidad,
       mes,
       sum(cantidad_item),
       sum(cantidad_item) * precio
  FROM nombre_de_la_tabla
 GROUP BY precio,
          departamento,
          unidad,
          mes
HAVING sum(cantidad_item) > 0