Count the number of occurrences of a value in different columns in * one * row / record

Question

Maybe I'm wrong when setting up the tables? I am using mysql and php. Downloading googleing shows how to count how many times a value appears in several rows, I want to check the value in many different columns, but in one record. (I think this is: counting the number of columns that have data for each row may be about the same thing, maybe, but I don't understand.)

I have a table with goals that I hope to work on and achieve every day. Therefore, for every day I want to note: "success" or "failure." And the insert works fine. Be that as it may, I am looking for a way to calculate the number of successes & the number of failures today to show the right kind of emoticons, which will be encouraging or sad, depending on the number of failures and the number of successes.

For instance:

ID date drinkMoreWater goToBedEarlier callADearFriend 1 2012 jan 15 fail fail fail 1 2012 jan 16 success _(still empty) success 

So, if today is jan 15, the emoticon will be very sad. If today is jan 16, the emoticon will be really really hppy with the stars in this eye (at least until I achieve goal 2;))

Answer 1

Your tables should be structured differently to make your life easier:

 Goals: ID Goal 1 drinkMoreWater 2 goToBedEarly 3 callADearFriend Status: ID Status 1 Success 2 Fail Tracking: ID Date Goal_ID Status_ID 1 1/1/2012 1 1 1 1/1/2012 3 2 1 1/2/2012 2 1 1 1/2/2012 4 1 

Now you can easily add goals and status (for example, “Work on it”), and your table structure should not be changed to fit your changes, your queries will become much simpler.

Answer 2

If you want to do this in SQL, you can use a triple construction, for example:

 SELECT *, (CASE drinkMoreWater WHEN 'success' THEN 1 ELSE 0 END) + (CASE goToBedEarlier WHEN 'success' THEN 1 ELSE 0 END) + (CASE callADearFriend WHEN 'success' THEN 1 ELSE 0 END) AS numberOfSuccesses FROM yourTable 

Answer 3

You can do something like this:

 Select case when sub.smilies = 0 then "very very sad" else case when sub.smilies = 1 then "sad" else "hubby" end end as "Number of Smilies" from ( Select case when t.drinkMoreWater = "fail" then 0 else 1 end + case when t.goToBedEarlier = "fail" then 0 else 1 end + case when t.callADearFriend = "fail" then 0 else 1 end as smilies from yourTableName t where date = @date ) sub 

You will need to handle empty lines and work with these case operations. This is a solution for your current design, but you'd better consider the redesign suggested by @BassamMehanni answer