CODEWITHSUNDEEP
mysqlmathoperations
Didina Deen
Didina Deen

Reputation: 195

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?

Upvotes: 1

Views: 36

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The query in your question is strange, because it has a math calculation without an aggregation function. And, you are aggregating by year and month, but not including them in the query.

I would be inclined to put the values in two separate columns, with the year and month explicitly defined in the query:

select year(utimestamp), month(utimestamp),
       sum(case when name = 'Valor1' then data*0.01 end) as valor1,
       sum(case when name = 'Valor2' then data*2000000 end) as valor2
from tData 
where utimestamp between '2016-01-01' and '2016-04-16' and
      name in ('Valor1', 'Valor2') and 
      data > 20
group by year(utimestamp), month(utimestamp)
order by max(utimestamp);

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

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

Upvotes: 1

Related Questions