Look up query HELP!

oldnickj · #1 (**permalink**) 10-21-10, 19:38

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 t

han 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

r937 · #2 (**permalink**) 10-21-10, 20:52

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

oldnickj · #3 (**permalink**) 10-23-10, 09:59

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

Nick

Internet Services

Web Development

Digital Marketing

Consumer Tech