I encountered a problem (or should I say an “opportunity”) where I needed to do some rounding in a query-of-a-query. This is normally very easy to do in most RDBMS, as there is usually some type of Round() function you can use. In my case I needed to round a percentage I was calculating in the Q of a Q down to only 1 decimal place. And I didn’t even really need to round it – I would be ok with simply truncating. Turns out this is a little difficult to do in a query of a query. There are no round() functions, no ceiling() or floor(), and no string manipulation functions. Here is what I came up with:
SELECT total, cast((total / #sumTotal#) * 1000 as integer) / 10 as percentage FROM qryPreviousQuery
So first I calculate the percentage by dividing the aggregate total of this row (‘total’, which was calculated in the original query) by the total of all records (calculated previously and placed into sumTotal). If we have 11 records in this row, out of 161 total records, that gives us 0.068322981366459627329192546583851 percent.
I don’t need this level of precision. So to round it down a bit, I multiply it by 1000, which gives us:
68.322981366459627329192546583851
Then, use ColdFusion’s CAST() function to convert this value to an int, which gives us 68.
Divide that by 10, and we end up where I needed to be: 6.8%. If you needed it expressed as a decimal, just divide it by 1000 instead of only 10.
