SQL query how to divide values from two columns from the same table in different condition

Supposed I have some sample data in table_name_a as below:

    code    val    remark   date
   -----------------------------------
1   00001    500    111      20191107
2   00001   1000    222      20191107
3   00002    200    111      20191108
4   00002    400    222      20191108

When I input code ‘00001’ and date ‘20191107’, I need the output as below:

        code    val    
        -------------
    1   00001   0.5

This SQL query must be wrong query, because I got wrong result as below:

         code    val
        --------------
0        00001   Null
1        00001   Null
2        00001   Null
3        00001   Null
4        00002   Null
5        00002   Null
6        00003   Null
7        00004   Null
8        00005   Null

SELECT 
    code, val
FROM 
    (SELECT 
         code, date,
         CASE WHEN t.remark = '111' THEN t.value ELSE 0 END /
         CASE WHEN t.remark = '222' THEN t.value ELSE 0 END AS val
     FROM 
         table_name_a t
     WHERE 
         code IN ('%(code)s')
         AND date IN ('%(input_date) s')) AS t1

Thanks so much for any advice.

Answer

try like below

select code, 
sum(case when remark=111 then val else 0 end)/sum(case when remark=222 then val else 0 end) as val
from table where code= '00001' and date= '20191107'
group by code

Advertisement

Answer