Results 1 to 2 of 2

Thread: Sum with Case

  1. #1
    Join Date
    Oct 2005
    Posts
    58

    Unanswered: Sum with Case

    How do I get 1 sum out of the following varchar fileld?

    Select 'USER_LEVEL' =
    CASE
    WHEN USER_LEVEL = 'Unlimited' THEN 0
    ELSE USER_LEVEL
    END

    From Table1

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by asahi
    How do I get 1 sum out of the following varchar fileld?

    Select 'USER_LEVEL' =
    CASE
    WHEN USER_LEVEL = 'Unlimited' THEN 0
    ELSE USER_LEVEL
    END

    From Table1
    Code:
    SELECT
      SUM(CASE
        WHEN USER_LEVEL = 'Unlimited' THEN 0
        ELSE USER_LEVEL
      END) AS USER_LEVEL
    FROM
      Table1
    But Wait!!!! You are testing for USER_LEVEL = 'Unlimited' (a string test), but then attempting to SUM the values of USER_LEVEL (if it's not 'Unlimited'). That dog won't hunt. You need a different expression in the ELSE clause.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •