Clever Geek Handbook

Comparing rows and calculating a column of one row

If you see 2 Abc then compute with sum like 9 * 2 = 18 and second line like 15 * 2 = 30 like the one that

add Fun1    Fun2    Fun3
9   Abc     Wxy     Abc
15  def     Abc     Abc
+3


source to share


2 answers


A number of expressions case

should do the trick:



SELECT add * ((CASE Fun1 WHEN 'Abc' THEN 1 ELSE 0 END) + 
              (CASE Fun2 WHEN 'Abc' THEN 1 ELSE 0 END) + 
              (CASE Fun3 WHEN 'Abc' THEN 1 ELSE 0 END)) AS total
FROM   mytable
+3


source


Used addn

instead of add

because it add

is a reserved word in SQL

SELECT addn, Fun1, Fun2, Fun3, ((CASE Fun1 WHEN 'Abc' THEN 1 ELSE 0 END) + 
                                (CASE Fun2 WHEN 'Abc' THEN 1 ELSE 0 END) + 
                                (CASE Fun3 WHEN 'Abc' THEN 1 ELSE 0 END)) * addn AS result
FROM yourtable

Output:



addn    Fun1    Fun2    Fun3    result
9       Abc     Wxy     Abc     18
15      def     Abc     Abc     30

SQL Fiddle: http://sqlfiddle.com/#!6/ec218/3/0

+2


source






More articles:


All Articles