Make a General Sum with all fields

Asked

Viewed 584 times

1

I have the following tables: tb_product, tb_movement

COD_PROD  | DESCRICAO | TIPO     | 
|01       | CEBOLA    | ALIMENTO |
|02       | ARROZ     | ALIMENTO |
|03       | CARNE     | ALIMENTO |
|04       | COCA-COLA | BEBIDA   |
|05       | PINGA     | BEBIDA   |

COD_MOV | COD_PROD | QUANT.| 
|01     |  01      | 5     |
|02     |  05      | 8     |
|03     |  02      | 10    |
|04     |  05      | 4     |
|05     |  03      | 1     |

sql

 select 
  CASE WHEN tb_produto.tipo = 'ALIMENTO' THEN
    sum(tb_movimento.QUANT*1)
  ELSE
    SUM(tb_movimento.QUANT*2)
  END
 from tb_movimento, tb_produto
 where (tb_produto.cod_prod = tb_movimento.cod_prod)
 group by tb_produto.tipo

My return:

|CASE|
|16  |
|24  |

In my case I would like to return to me:

|CASE|
|40  |
sql firebird

  • It’s not just taking the group by?

  • If I take out groyp by it does not run "Invalid Expression in the select list (not contained in either an Aggregate Function or the GROUP BY clause)."

2 answers

2


As it has two types of products and select is grouped by types of product it is natural that it displays the result in this way.

I don’t have FIREBIRD to test and I’m posting a tested response on Oracle, as I recall, runs on your bank without any problems.

If you have problems in your post bank here we fix.

Do so:

select sum( total ) from (  
 select 
  CASE WHEN tb_produto.tipo = 'ALIMENTO' THEN
    sum(tb_movimento.QUANT*1)
  ELSE
    SUM(tb_movimento.QUANT*2)
  END as total
 from tb_movimento, tb_produto
 where (tb_produto.cod_prod = tb_movimento.cod_prod)
 group by tb_produto.tipo ) a

0

Do so:

select sum(tb_movimento.QUANT * (case when tb_produto.tipo='ALIMENTO' then 1 else 2 end)) from tb_movimento inner join tb_produto on tb_produto.cod_prod = tb_movimento.cod_prod

Improvement: Case used to define the multiplication factor.

Browser other questions tagged sql firebird

You are not signed in. Login or sign up in order to post.