CODEWITHSUNDEEP
phpmysqlcount
Alisso
Alisso

Reputation: 1902

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

I might be doing this the wrong way when I set up the tables? I'm using mysql & php. Loads of googleing only shows how to count how many times a value appears in several rows, I want check for a value in many different columns but in the same record. (I think this: count number of columns that have data for each row just might be about the same thing, maybe? but I don't get it.)

I have a table with goals that I hope to work on and achieve every day. So for every day I wish to mark: "success" or "fail". And insert is working great. How ever I'm looking for a way to calculate the number of success& number of fails of "today", to show the right kind of smiley which will be encouraging or sad depending on number of fails and number of successes.

For example:

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 smiley will be very very sad. If today is jan 16 the smiley will be really really hppy with stars in it's eye's (atleast until I fail goal 2 ;) )

Upvotes: 4

Views: 392

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

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 the empty strings, and work around these case statements. This solution is for your current design but you better off consider the redesign suggested by @BassamMehanni 's answer

Upvotes: 0

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

Your tables should actually 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 could easily add goals and status (e.g. 'Working on it') and your table structure does not have to change to accommodate your changes, your queries become a lot simpler as well.

Upvotes: 9

linepogl
linepogl

Reputation: 9335

If you want to do that in SQL, you could use a ternary-like construct, like that:

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

Upvotes: 0

Related Questions