jbird468 jbird468 - 6 months ago 9
SQL Question

SQL generate score column from multi-condition test on a notes cell

Basically I have a table with a notes column and I want to generate a column that looks for a few conditions and spits out a score based on how many conditions were met when looking at each note. I may be going about this the wrong way so please feel free to tangent.

Query so far:

SELECT
SUM(
SUM(case when notes like '%Tuna%' THEN 1 ELSE 0 END)
SUM(case when notes like '%apple%' THEN 1 ELSE 0 END)
SUM(case when notes like '%burrito' THEN 1 ELSE 0 END)
-- ) as score
,Name
,Date
,Notes
FROM food_jrnl


Example table food_jrnl:

Name Date Note
Peter 6/1/2016 Just mountain Dew and cheatos
Jimmy 5/25/2016 Chocolate cake, cheesy potatoes and ketchup
Sophie 5/16/2016 just grits and tuna!!
Bianca 5/9/2016 Chocolate milk, Ahi tuna, Gala apple
Sam 4/23/2016 Tuna salad
Josh 1/10/2016 Had a banana and apple with orange juice


What I am hoping to create:

Score Name Date Note
0 Peter 6/1/2016 Just mountain Dew and cheatos
0 Jimmy 5/25/2016 Chocolate cake, cheesy potatoes and ketchup
1 Sophie 5/16/2016 just grits and tuna!!
2 Bianca 5/9/2016 Chocolate milk, Ahi tuna, Gala apple
1 Sam 4/23/2016 Tuna salad
0 Josh 1/10/2016 Had a banana and apple with orange juice

Answer

Actually you don't need to use sum for this:

SELECT
    case when notes like '%Tuna%' THEN 1 ELSE 0 END +
    case when notes like '%apple%' THEN 1 ELSE 0 END +
    case when notes like '%burrito' THEN 1 ELSE 0 END as score
    ,Name
    ,Date
    ,Notes
FROM food_jrnl