We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SQL syntax for calculations

EBuffey
EBuffey asked
on
Medium Priority
201 Views
Last Modified: 2010-05-18
What is the syntax for creating conditional calculations?
For example:
  select sum(if (Field1 = 'x') then 8 else 1)
  from Table1

I'm accustomed to Oracle SQL which would format the above as:
  select sum(decode(Field1, 'x', 8, 1))
  from Table1
Comment
Watch Question

Commented:
Hi EBuffy,
Don't think is possible.
Believe you can only use Sum(ALL Field1) or Sum(DISTINCT Field1).
I think you better make calculated fields where in you make your
conditions. Then you Sum these calculated fields.
Have fun,
c.u. ZifNab;
EBuffey,
I'm not sure what your example statement is trying to accomplish (in real life) but I'll do my best. As I do not know what database you are *wanting* to use, I'll assume MS SQL Server

Select Answer =
   Case
     WHEN Field1 = 'x' then 8
    else 1
from Table1

The previous example is more commonaly used when a field needs to be calculated and checked against a result such as

...
WHEN (Field1 * 30 < 400) then blah, blah blah
...

If you have a condition simply based on the value of a field (such as Yes or No) then the following would be more appropriate

Select Answer =
   Case Field1
            WHEN 'Yes' then 'YesAnswer'
            WHEN 'No' then 'NoAnswer'
           else 'Bad_Value_In_Field1'
from Table1

Note that the value following the THEN statement need not be a simple constant. It could be another select statement, another Case statement, etc. Some of my queries (for financial purposes) run in excess of 12 pages with many, many levels of conditional statements.

If prefer the case statement for my conditional SQL as opposed to the IF statement. Personal preference :)

Hope this helps, if not... hey! Can't blame a guy for trying!

tnorris

Author

Commented:
Pegasus's answer looked great but it didn't work! The reason is that I'm using local SQL to access a Paradox table. (i.e. from Database Desktop)

I got the infamous "Invalid use of keyword" message! Which (if Borland is listening) is an absolutely USELESS error message when there is no examples in the help files nor in the Borland manuals that come with Delphi 2.0!!!

Author

Commented:
Since my "real life" query uses a numeric field, I was able to solve my own problem with the following SQL! However, I'd still like to know if there is a way to do this using an alpha field!

  select sum((Field1 / 64) * 7 + 1) as DesiredTotal
  from Table1

The above query will use a value of 1 when Field1 < 64 and use a value of 8 when Field1 >= 64.
EBuffey,

Sorry my previous answer didn't work. I don't work with Local SQL much but your question intriegued me so I went and had a look.

Your answer is: It cannot be done in Local SQL because this "version" of SQL does not contain any coditional select statements. My recommendation would be to get as close as you can with the simplistic SQL they give you and tdo the rest in your program.

Sorry,
Tracy

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.