Sum of quantity multiplied by product value in daily sales

Asked

Viewed 678 times

1

I have an order page, let’s take as a base a mercantil, where the person buys 3 sodas each with the value of R $ 6,00 and 2 cookies in the value of R $ 2,00 each.

First I need to multiply these values (3 * 6) and (2 * 2) and then add their results, which would give a total purchase amount of R $ 22,00 as well as bring the name of the customer who made the purchase.

I’m using the Eloquent of Laravel, but I’ve been finding some difficulties in the elaboration of the query, if you can help me, follow the code and the image of the relationship 

$vendas = Venda::where('data', date('Y-d-m'))
        ->join('clientes', 'vendas.cliente_id', '=', 'clientes.id')
        ->join('produtos_venda', 'vendas.id', '=', 'produtos_venda.venda_id')
        ->select('vendas.*', 'clientes.nome as nome_cliente', 'produtos_venda.quantidade', 'produtos_venda.valor')
        ->sum('produtos_venda.valor * produtos_venda.quantidade as valor_total')->get();

Relacionamento das tabelas

mysql laravel laravel-5 laravel-eloquent

  • What is the expected result?

  • receive only customer name, sale date and total value

1 answer

2


The right way would be:

$select = "sum(produtos_venda.valor*produtos_venda.quantidade) as total,";
$select .= "vendas.cliente_id, clientes.nome, vendas.data";

$vendas = Venda::where('data', date('Y-d-m'))
        ->join('clientes', 'vendas.cliente_id', '=', 'clientes.id')
        ->join('produtos_venda', 'vendas.id', '=', 'produtos_venda.venda_id')
        ->groupBy('vendas.cliente_id', 'clientes.nome', 'vendas.data')
        ->select(\DB::raw($select))
        ->get();

Remarks:

  • In the product table, I change the field type preco for decimal(10,2)
  • In the sales table, I change the field type data for date
  • In the product table_sales change the type of the value field to decimal(10,2)
  • If by chance you think decimal(10,2), is insufficient to increase the number 10 to 12

These observations can and should be followed, if you have the corresponding type because you use a type that can disturb even the operations of sum, multiplication, etc and filters with dates, then always put the corresponding types.

References:

Browser other questions tagged mysql laravel laravel-5 laravel-eloquent

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