MySQL: multiple conditions to different columns in MySQL

Question

MySQL: multiple conditions to different columns in MySQL

Navigation

#1 by (1 votes)

0

I want this question to come out in a single row:

 select (case month(p.fechaVent)
 when 1 then sum(p.totalVent)
end) AS "ENERO",(case month(p.fechaVent)
 when 2 then sum(p.totalVent)
 end) AS "FEBRERO"
 from proformas p
   group by  month(p.fechaVent)

As you can see in the attached image I get with null values, I wish that the null values do not come out and only the data comes out in a row, and I thought about doing it with a kind of subquery to each column that I add but the truth is that I'm looking for info but I can not find what I need, a push please.

mysql query

asked by Alvaro Molina Cruz 03.07.2017 в 21:09

source

1 answer

Hide / Show TextBox when the value of another TextBox changes Git 'nothing to commit, working tree clean'

score 1 · Answer 1

In that case you should only use a subquery.

select sum(ifnull(ENERO,0)) as ENERO, sum(ifnull(FEBRERO,0)) as FEBRERO -- Los otros meses...
from (
 select 
   (case month(p.fechaVent) when 1 then sum(p.totalVent) end) AS "ENERO",
   (case month(p.fechaVent) when 2 then sum(p.totalVent) end) AS "FEBRERO"
   -- Los otros meses..
 from proformas p 
 group by month(p.fechaVent)
) as subconsulta;

Explaining:

select 
  (case month(p.fechaVent) when 1 then sum(p.totalVent) end) AS "ENERO",
  (case month(p.fechaVent) when 2 then sum(p.totalVent) end) AS "FEBRERO"
  -- Los otros meses..
  from proformas p 
  group by month(p.fechaVent)

The previous code performs the query you propose in the question, returning a table more or less like this.

+-----------+-----------+------+                                                                                                                                                                                        
| ENERO     | FEBRERO   | ...  |                                                                                                                                                                                        
+-----------+-----------+------+                                                                                                                                                                                        
| 100.000   | NULL      | ...  |                                                                                                                                                                                        
+-----------+-----------+------+                                                                                                                                                                                        
| NULL      | 65.000    | ...  |                                                                                                                                                                                        
+-----------+-----------+------+                                                                                                                                                                                        
| NULL      | NULL      | ...  |                                                                                                                                                                                        
+-----------+-----------+------+                                                                                                                                                                                        
| NULL      | NULL      | ...  |                                                                                                                                                                                        
+-----------+-----------+------+                                                                                                                                                                                        
| NULL      | NULL      | ...  |                                                                                                                                                                                        
+-----------+-----------+------+                                                                                                                                                                                        
| ...       | ...       | ...  |                                                                                                                                                                                        
+-----------+-----------+------+

Now you have a new table and therefore you can make a query about it, it would only suffice to add the rows of each column, but nevertheless you should consider that:

sum(ENERO) -- El resultado será NULL 
-- puesto  que 100.000 + NULL + ... + NULL = NULL

Then you should only convert to zero (0) everything that is null

sum(ifnull(ENERO,0)) -- Siempre se completa primero la función interna 
-- Ahora 100.000 + (0) + ... + (0) = 100.000

Then considering the above

select sum(ifnull(ENERO,0)) as ENERO, sum(ifnull(FEBRERO,0)) as FEBRERO -- Los otros meses...
from (...) -- Donde (...) es la tabla resultado de la consulta que describimos arriba

You will see that the table that we described above "does not really exist", since it is a query, now the result of a query is a table so it can be consulted about it, therefore the only thing that is subtracting is giving it a name alias to that query only as for fulfilling good practices.

select sum(ifnull(ENERO,0)) as ENERO, sum(ifnull(FEBRERO,0)) as FEBRERO -- Los otros meses...
from (...) as subconsulta;

I hope it's a bit illustrative for you, here you can find more information link .