Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Look up query HELP!

    In table B I have 3 apps with three records each.
    I need a query which will return "yellow.gif" for a sum of the three scores is 0 (as in 3070)
    and "red.gif" if the sum is 1 or more and less than 9 (as in 3059)
    and "green.gif" if the sum of the 3 app scores is 9 or more (as in 3100)

    TBL A
    id val txt
    92 1 yellow.gif
    93 2 red.gif
    94 3 green.gif

    TBL B
    ID app score

    11 3100 3
    13 3100 3
    12 3100 3
    16 3059 1
    17 3059 2
    15 3059 1
    18 3070 0
    19 3070 0
    20 3070 0

    The result needs to look like:

    3070 yellow.gif
    3059 red.gif
    3100 green.gif

    Thanks for your help,

    Nick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT app
         , CASE WHEN SUM(score)= 0
                THEN 'yellow.gif'
                WHEN SUM(score) BETWEEN 1 AND 8
                THEN 'red.gif'
                WHEN SUM(SCORE) >= 9
                THEN 'green.gif'
                ELSE NULL END   AS signal
      FROM daTable
    GROUP
        BY app
    i'm gonna guess you will want to switch your yellow and red

    i've only ever seen those three colours with yellow in the middle, never red in between yellow and green
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    124

    Great!

    Thanks, very clean and simple, I never considered CASE. I'll read the chapter in our book " Simply SQL" (sitepoint press)
    Thank you

    Nick

Posting Permissions

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