SQL Lite: I want to add 1 everytime a criteria is met-CodePudding

So i want to check if a criteria is met and then add 1 everytime.

i tried it like this

SELECT Track,
 CASE 
 WHEN Duration like ':%' then metrics_count   1
 WHEN Danceable = 'not very danceable' then metrics_count   1
 WHEN Energy = 'high energy' then metrics_count   1
 WHEN Emotion = 'moody' THEN metrics_count   1
 WHEN  "Key" = 'C#' THEN metrics_count   1
End as metrics_count
FROM
 table

but i just get nulls or 1 so it doest add up it restarts everytime i think.

CodePudding user response：

Looks you need next

SELECT Track,
    (case when Duration like ':%' then 1 else 0 end)  
    (case when Danceable = 'not very danceable' then 1 else 0 end)  
    (case when Energy = 'high energy' then 1 else 0 end)  
    -- other cases
    (case when Emotion = 'moody' then 1 else 0 end) as metrics_count
FROM
 table

SQLite online test