Query with two summations for the same column

Asked

Viewed 118 times

0

It is possible to make a query in which in the same query two sums are executed on the same column?

Here’s what I got:

Total sum of sales

select month(emitido_date) as mes, ifnull(sum((det.preco * det.quantidade) * (iva.valor/100) + (det.preco * det.quantidade) - (det.preco * det.quantidade * (det.desconto/100))),0) as total
from documento as doc
inner join documento_serie as serie on serie.id = doc.documento_serie_id
inner join documento_detail as det on doc.id = det.documento_id
inner join phos_iva as iva on iva.id = det.iva_id
where serie.documento_categoria_id = 3  and doc.rascunho = false and doc.exercicio_id = 4 
group by mes
order by mes

Sum Sales Liquidated

select month(emitido_date) as mes, ifnull(sum((det.preco * det.quantidade) * (iva.valor/100) + (det.preco * det.quantidade) - (det.preco * det.quantidade * (det.desconto/100))),0) as total
from documento as doc
inner join documento_serie as serie on serie.id = doc.documento_serie_id
inner join documento_detail as det on doc.id = det.documento_id
inner join phos_iva as iva on iva.id = det.iva_id
where serie.documento_categoria_id = 3  and doc.rascunho = false and doc.exercicio_id = 4 and (serie.documento_tipo_id = 10 or serie.documento_tipo_id = 11 or serie.documento_tipo_id = 15)
group by mes
order by mes

1st question. It is possible to have the return of a table |Month|totalVendas|totalLiquidado| everything only in a query?

Question 2. In the second query presented, in the clause where, when the field serie.documento_tipo_id for 15, no inner join table documento_detail the field cannot be the doc.id but rather the doc.source_id. How can I put this condition?

mysql sql query mysql-workbench

  • You already tested those query’s?

  • Yes these query’s are working, only in the second it does not add the value if the document is of type 15.

  • Hugo changes the 15 of place with the 11 and it verifies if it ceases to add the documents of the type 11.

  • Yes it adds up the 11, only it does not add up the 15 as explained in the second question.

  • experiment with and serie.documento_categoria_id IN(10,11,15)

  • I am in mysql and this IN gives error. I don’t know if what I say will help, but doc 15 has nothing that corresponds to it in the table documento_detail, so it can only get its value from doc.source_id

  • So it’s normal that nothing goes missing.

  • Yes it is normal that nothing goes missing, but I want it to go away, because its value comes from doc.source_id in this specific case.

  • Let’s go continue this discussion in chat.

Show 4 more comments

1 answer

0

Yes, it is possible to do this without resorting to a subquery.

  • Since "Sales Settled" is a subset of "Sales Total" you should use the first query as the basis for what you want.
  • Uses a CASE to restrict sales in the calculation of the total "Liquidated Sales"
  • For the condition "when the field serie.documento_tipo_id is 15, in the inner Join of the table documento_detail the field cannot be doc.id but doc.source_id" just use one more time CASE.

At the end the query would look like this:

select month(emitido_date) as mes, 
      ifnull(sum((det.preco * det.quantidade) * (iva.valor/100) + (det.preco * det.quantidade) - (det.preco * det.quantidade * (det.desconto/100))),0) as totalVendas,
      ifnull(sum(case when serie.documento_tipo_id in (10, 11, 15) then (det.preco * det.quantidade) * (iva.valor/100) + (det.preco * det.quantidade) - (det.preco * det.quantidade * (det.desconto/100)) else 0 end),0) as totalLiquidado       
from documento as doc
inner join documento_serie as serie 
   on serie.id = doc.documento_serie_id
inner join documento_detail as det 
   on case when serie.documento_tipo_id = 15 then doc.source_id = det.documento_id else doc.id = det.documento_id end
inner join phos_iva as iva 
   on iva.id = det.iva_id
where serie.documento_categoria_id = 3  
  and doc.rascunho = false 
  and doc.exercicio_id = 4 
group by mes
order by mes

Browser other questions tagged mysql sql query mysql-workbench

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