How can I do math operations in query SELECT?

I have a table of statistics where operations with water are counted. I have three types of water 0,2,3. But there is water type 0, which includes type 2, type 3 and type 0, that is, water type 0 = 0 + 2 + 3. That is, if an operation with a water type for example 2, then in the database I will have a record of an operation with water type 2 and an operation with water type 0. I have no real records only with type 0. I have a period marker, when the period is 1, I keep count. How do I sample for water type 0, it should be something like this 0 = 0-2-3. I tried to write something like this

SELECT 
(SELECT water FROM `production`.statistics where statistical_id = 3133 and created_at >= '2020-10-04 21:00:00' and created_at <= '2020-10-15 21:00:00' and  water_type = 0 and period=1) - 
(SELECT water FROM `production`.statistics where statistical_id = 3133 and created_at >= '2020-10-04 21:00:00' and created_at <= '2020-10-15 21:00:00'  and  water_type = 2 and period=1 )-
(SELECT water FROM `production`.statistics where statistical_id = 3133 and created_at >= '2020-10-04 21:00:00' and created_at <= '2020-10-15 21:00:00'  and  water_type = 3 and period=1 );

but if there is no operation with some of the types of water, I get the result null. I tried to apply EXIST, but I was unable to apply it, I constantly get an incorrect syntax error. I use Maria DB.

Answer

You can simplify this query:

select sum(case when water_type = 1 then water
                else - water
           end)
from `production`.statistics 
where statistical_id = 3133 and
      created_at >= '2020-10-04 21:00:00' and
      created_at <= '2020-10-15 21:00:00' and
      period = 1 and
      water_type in (1, 2, 3);

This should also solve the NULL problem.

Advertisement

Answer