CODEWITHSUNDEEP
mysqldifference
mpsbhat
mpsbhat

Reputation: 2773

how to subtract two resultant values in mysql

Say I have a table tableA and my query is 

select id, 
       if(cond1, value, 0) firstval, 
       if(cond2, value, 0) secondval, 
       firstval-secondval diff 
from tableA

The above query gives Unknown column firstval in field list error. I know I can calculate diff as if(cond1, value, 0)-if(cond2, value, 0) diff but i don't want to add condition again and without inner/sub queries.

EDIT: My abstract idea as follows

Table structure

id   |    type   |  recorddate  | value
=========================================
 1         A       2015-12-17       9
 2         B       2015-12-19       5
 3         A       2016-01-13       31
 4         B       2016-01-14       23
 5         A       2016-01-31       44
 6         B       2016-02-07       38

and so on...

Query:

 select 
    type, 
    if(max(recorddate), value, 0) firstval, 
    if(secondmax(recorddate), value, 0) secndval, 
    firstval-secndval diff 
 from table 
 where month(recorddate)=1
 group by type with rollup

Resultant table based on above query:

type  | firstval  |  secndval  |  diff
======================================
 A      44           31          13
 B      23           5           18
Total   67           36          31

Upvotes: 0

Views: 393

Answers (2)

Kaja Mydeen
Kaja Mydeen

Reputation: 575

Do you want to get subtract two resultant values. Am i right?

So that for firstval check with one condition if condition true then get that value otherwise firstval value is 0. Do the same for secondval also. So that you will not get unknown column error. First of all you should have column name firstval and secondval.

Select firstval-secondval as total from tableA;

It will work for simple subtract.

Upvotes: 0

splash58
splash58

Reputation: 26153

Add sub-query

select *, firstval-secondval diff 
   from 
     (select id, if(cond1, value, 0) firstval, if(cond2, value, 0) secondval
        from tableA
     ) t

Upvotes: 2

Related Questions