0

I have a MySQL database, it contains a table that contains some data in columns of type varbinary(20)

Don't ask me why - it just is and i need to deal with it.

This data represents the number of modules a student has completed as part of a course. There is also another column called criterias that represents the number of modules that must be completed in order for the course to be marked complete. When I am doing a count of the number of completed courses, I want to count the rows where 

completed = criteria AND completed != 0 ( ...oh and where the student is enrolled)

(because in some cases the criteria is 0 so I want to skip any courses where a student has completed 0 of 0 modules).

Additionally for some reason the value of 0 in the varbinary column is 30 (if anyone can offer an explanation for this then great) and so where there are 3 modules that need to be completed the value in the criteria column will be 33

So my SQL query looks like this - 

count(CASE WHEN (completed <> 30  AND (completed = criterias) AND enrolled = 'Yes') THEN 1 END) AS Total_Complete

However this count seems to include courses where student has completed 0 of 0 courses so it would appear that my completed <> 30 is not working.

Hunter McMillen
  • 59,865
  • 24
  • 119
  • 170
undefined
  • 5,190
  • 11
  • 56
  • 90
  • Ah well spotted - yes I was using <> but i typed the wrong thing in the question title. I have updated the question to include the <> operator – undefined May 21 '14 at 15:35

1 Answers1

0

As I was writing this I answered my own question but I thought id leave it up here incase it helped anyone else.

The solution was to ignore the fact that for whatever reason 0 was represented by 30 and change my sql to - 

count(CASE WHEN (completed <> 0 AND (completed = criterias) AND enrolled = 'Yes') THEN 1 END) AS Total_Complete

This worked

undefined
  • 5,190
  • 11
  • 56
  • 90