CODEWITHSUNDEEP
sqlselectmultiple-conditions
jbird468
jbird468

Reputation: 11

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

Upvotes: 1

Views: 226

Answers (1)

sgeddes
sgeddes

Reputation: 62851

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

Upvotes: 2

Related Questions