putting IF logic into a calculated column?

With the below query, is it possible to insert some logic which would output a text string into the next column depending on the value of the last column (which is calculated)

e.g. "small" if value <= 10, otherwise "large"

Or do we need to do some logic in TSQL for this?
i.e. open up a cursor, store the value of col#4 in a variable, do our logic on it, and output a string using PRINT depending on its value ?

cheers!
select EA.ex_pi_eval_answer_school_id, 
SUM(CAST(EA.ex_pi_eval_answer_response AS DECIMAL)), 
COUNT(EA.ex_pi_eval_answer_response),
SUM(CAST(EA.ex_pi_eval_answer_response AS DECIMAL)) / COUNT(EA.ex_pi_eval_answer_response)

from tbl_ex_ProgramImpact_eval_answers EA

where EA.ex_pi_eval_answer_ProgramObjective_id = 1

group by EA.ex_pi_eval_answer_school_id

Open in new window

zorba111Asked:
Who is Participating?
 
vdr1620Connect With a Mentor Commented:
you can use a CASE Statement Like

OUTPUTColumn = CASE WHEN Value<=10 THEN 'Small' ELSE 'Large' END

The below blog has some good explanation with examples
http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/
0
 
zorba111Author Commented:
NB typical output from the current query is like:

2036449      19      5      3.8
4010885      22      10      2.2
1116159      23      5      4.6
2046646      23      5      4.6
4036081      23      5      4.6
1016647      22      5      4.4
2036149      21      5      4.2
4230165      20      5      4

(excel, which I've used as an intermediary to copy this data, has stripped out non-significant trailing zeros)
0
 
zorba111Author Commented:
@vdr1620

Hi this is great, however having read up on CASE WHEN, I fear it will not work.

I had simplified my problem somewhat, so its not your fault!

There are actually three ranges (NOT two) that the values could fall into, and for each value, a different string gets output :-(
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
vdr1620Connect With a Mentor Commented:
Not a problem..you can use

CASE WHEN (condition) THEN Statement
          WHEN (condition) THEN Statement
           WHEN (condition) THEN Statement END

OUTPUTColumn = CASE WHEN Value<=10 THEN 'Small'
                                        WHEN Value >10 THEN 'Large'
                                        WHEN Value >100 THEN 'SomeotherValue'        END

0
 
zorba111Author Commented:
@vdr1620

hmm, having looked at Pinal Dave's page (the link you kindly referred me to), I think now that all may not be lost!

I will report back....
0
 
zorba111Author Commented:
ok, it wasn't in his blog, but in MSDN I found this usage....

SELECT    'Price Category' =
      CASE
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You can also do something like this, if you need to:

SELECT
    CASE WHEN price IS NULL THEN '...'
              WHEN price < 10 THEN '...'
              WHEN price >= 10 AND price < 20 THEN '...'
              ELSE '...' END +
    ',' +
    CASE
              WHEN size < 10 THEN 'Small'
              WHEN size > 10 AND size < 20 THEN 'Medium'
              WHEN size >= 20 THEN 'Large'
              ELSE 'Unknown size' END
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.