CODEWITHSUNDEEP
sqlsql-server
Ritesh
Ritesh

Reputation: 13

Arithmetic operation on row value

I have a table with the below data

Tid   Did    value
------------------
1     123    100
1     234    200
2     123    323
2     234    233

All tids have dids as 123 and 234. So for every tid having dids 123 and 234 I want to calculate value of did 123/value of did 234 * 100 i.e 100/200 * 100 For tid 2 it will be value of did 123/value of did 234 * 100 i.e 323/233 * 100

The output table will be

Tid   result
------------------
1     100/200 * 100
2     323/233 * 100

Any help?

Upvotes: 0

Views: 138

Answers (4)

juergen d
juergen d

Reputation: 204854

select tid,
       100 * sum(case when did = 123 then value end) / 
             sum(case when did = 234 then value end)
from your_table
group by tid
having sum(case when did = 234 then value end) > 0

Upvotes: 0

RBT
RBT

Reputation: 25945

Here is the query. We can use inner join to achieve it.

SELECT T1.Tid,(T1.value/T2.value)*100 AS Result 
FROM Table_1 AS T1
INNER JOIN
Table_1 AS T2
ON (T1.Tid = T2.Tid) 
AND (T1.Did <> T2.Did) 
AND T1.Did = 123

Upvotes: 0

Serg
Serg

Reputation: 22811

JOIN, all in ON 

select t123.tid, t123.value * 100 / t234.value
from tablename t123
join tablename t234 on t123.tid = t234.tid and t123.did = 123 and t234.did = 234

Upvotes: 0

jarlh
jarlh

Reputation: 44786

JOIN the "123" rows with the "234" rows:

select t123.tid, t123.value * 100 / t234.value
from
  (select tid, value from tablename where value = 123) t123
join
  (select tid, value from tablename where value = 234) t234
  on t123.tid = t234.tid

Upvotes: 1

Related Questions