Query with two summations for the same column

Question

Query with two summations for the same column

Navigation

#1 by (0 votes)

0

Is it possible to do a query where two sums are executed on the same column in the same query?

What I have is the following:

Total sales sum

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 of Liquidated 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 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 issue. It is possible to have the return of a table | Month | totalSales | totalLiquid | everything just in a query?

2nd issue. In the second query presented, in the where clause, when the serie.documento_tipo_id field is 15, in the inner join of the documento_detail table the field can not be doc.id , but doc.source_id . How can I put this condition?

mysql sql query mysql-workbench

asked by anonymous 11.08.2015 / 10:33

1 answer

Position of components with bootstrap How to show table results always at the top?

score 0 · Answer 1

Yes, you can do this without resorting to a subquery.

Since "Liquidated Sales" is a sub-set of "Total Sales" you should use the first query as the basis for what you want.
Use a CASE to restrict sales when calculating total "Sales Liquidated"
For the condition "when the serial field.documento_type_id is 15, in the inner join of the document_detail table the field can not be doc.id but doc.source_id" just once again use a CASE .

In the end the query looks 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