The detritus of a Button Monkey

31 Mar

Problems with MAX() and MIN() with MySQL

I have been playing around with some data manipulation and performing some basic stats on the data. I used this SELECT statement as a starting point:

SELECT Round(STDDEV(Result),2), Round(AVG(Result),2), COUNT(Result), Round(MIN(Result),1), Round(MAX(Result),1)

This allowed me get the standard deviation, mean and number of results. The Round refers to rounding the result and the number is the number of decimal places required. It took me a while before I realized that I didn’t need the Round function for MIN and MAX as they are going to be single results and therefore wouldn’t have numerous unwanted decimal places.

The problems started when I got the statement within a working php page.  On some tests, it was working fine and on some pages it was producing some bizarre results i.e. MAX values lower than the MIN values.  I tired various adjustments to the SQL SELECT statement but with no success. I knew I had to be close as the script worked  on certain data sets.

I then began to think my data was corrupted in some way, that this corruption might cause the strange MIN greater than MAX I was seeing.  Then I realized that maybe the  script was working fine after all.  The examples where it didn’t work involved numbers like 10 or 100.  These were being returned as the minimum values and values of maybe 9.2 or 99.8 were being returned as the MAX values. I realized that in this examples, the data was being evaluated as a string and not as numerical data.  I then looked at the table structure and the Result field was setup as a VARCAHR(8) field.  Changing this to a numerical field e.g. INT allowed the data to be evaluated as numerical data and therefore return correct MIN and MAX values.

Leave a Reply

You must be logged in to post a comment.

© 2022 The detritus of a Button Monkey | Entries (RSS) and Comments (RSS)

Design by Your Index - Powered By Wordpress