Didina Deen Didina Deen - 5 months ago 7
SQL Question

What's the most efficient way of operating with fields in MySQL?

I have the following query:

SELECT DATE(utimestamp) as utimestamp, name, data*2000000 from tData
where utimestamp BETWEEN '2016-01-01 00:00:00' AND '2016-04-16 00:00:00'
AND name = 'Valor2' and data>20
group by YEAR(utimestamp), MONTH(utimestamp), name
union
SELECT DATE(utimestamp) as utimestamp, name, data*0.1 from tData
where utimestamp BETWEEN '2016-01-01 00:00:00' AND '2016-04-16 00:00:00'
AND name = 'Valor1' and data>20
group by YEAR(utimestamp), MONTH(utimestamp), name
order by utimestamp asc


Is there a more efficient way of operating with 'data'? Is there a way of doing this without using UNION?

Answer

You can try to use case when then:

SELECT DATE(utimestamp) as utimestamp, name, 
case when name = 'Valor1' then data*0.1
     when name = 'Valor2' then data*2000000 
end
from tData 
where utimestamp BETWEEN '2016-01-01 00:00:00' AND '2016-04-16 00:00:00' 
and data>20 
group by YEAR(utimestamp), MONTH(utimestamp), name 
order by utimestamp asc