Query table in two columns but one in mysql

Question

Query table in two columns but one in mysql

Navigation

#1 by (1 votes)

0

I have a table that has inside its columns: period (2017-10, 2017-11, etc.) amount_box (values) billed (has "yes" and "no"). I need to make a table with 4 columns: THE period, the unbilled amount per period, the amount billed per period, and the sum of both. I currently have this query:

SELECT periodo AS PERIODO, 
(select  sum(capsur_vtr.monto_caja) AS 'NO FACTURADO' from capsur_vtr  
WHERE periodo not like '%proforma%' and facturado = 'SI'
) AS FACTURADO, 
(select 
sum(capsur_vtr.monto_caja) AS 'NO FACTURADO' from capsur_vtr
WHERE periodo not like '%proforma%' and facturado = 'NO'
) AS NO_FACTURADO, 
sum(capsur_vtr.monto_caja) AS TOTAL FROM capsur_vtr
where periodo not like '%proforma%'
group by periodo
order by periodo

Which throws me this:

Obviously I am taking the total within each cell of the invoiced and unbilled columns (the sum of the cells of both columns in a row is equal to the cumulative of the total) How do I make the value in each cell the only sum of the period?

Thanks for your help. Rodrigo.

mysql consulta-sql

asked by Rodrigo Neira 13.07.2018 в 22:20

source

1 answer

How to do this with ORM Laravel Error trying to empty CKeditor content

score 1 · Accepted Answer

If I understand correctly, you can try to add based on condition, I'll give you an example:

SELECT periodo AS PERIODO,
SUM(IF (c.facturado = "SI",c.monto_caja,0)) AS FACTURADO,
SUM(IF (c.facturado = "NO",c.monto_caja,0)) AS NO-FACTURADO,
SUM(c.monto_caja) AS TOTAL
FROM capsur_vtr c
WHERE c.periodo NOT LIKE '%proforma%'
GROUP BY c.periodo ORDER BY c.periodo