Crosstab aggregate error, A Stumper!
-
I'm scratching my head on this one...What's wrong?
Here's the SQL:
SELECT cas_no , SUM(CASE WHEN cas_lbs < 100 THEN cas_lbs ELSE 0 END) HundredSum , SUM(CASE WHEN cas_lbs < 100 THEN 1 ELSE 0 END) HundredCount , SUM(CASE WHEN cas_lbs < 10 THEN cas_lbs ELSE 0 END) TenSum , SUM(CASE WHEN cas_lbs < 10 THEN 1 ELSE 0 END) TenCount , SUM(CASE WHEN cas_lbs < 1 THEN cas_lbs ELSE 0 END) OneSum , SUM(CASE WHEN cas_lbs < 1 THEN 1 ELSE 0 END) OneCount , SUM(CASE WHEN cas_lbs < .1 THEN cas_lbs ELSE 0 END) TenthSum , SUM(CASE WHEN cas_lbs < .1 THEN 1 ELSE 0 END) TenthCount --Everything above this point returns correct results --Everything below this point returns the same results as the < 0.1 entry... , SUM(CASE WHEN cas_lbs < .01 THEN cas_lbs ELSE 0 END) HundredthSum , SUM(CASE WHEN cas_lbs < .01 THEN 1 ELSE 0 END) HundredthCount , SUM(CASE WHEN cas_lbs < .001 THEN cas_lbs ELSE 0 END) ThousandthSum , SUM(CASE WHEN cas_lbs < .001 THEN 1 ELSE 0 END) ThousandthCount , SUM(CASE WHEN cas_lbs < .0001 THEN cas_lbs ELSE 0 END) TenThousandthSum , SUM(CASE WHEN cas_lbs < .0001 THEN 1 ELSE 0 END) TenThousandthCount FROM t_ssvocl --WITH (NOLOCK) WHERE cas_lbs < 100 AND cas_no = 107835 GROUP BY cas_no ORDER BY CHAR(LEN(cas_no) + 60) + CAST(cas_no AS varchar(12))
I find it very strange that the whole numbers AND .1 work correctly, it's just the .01 and below that don't. I figure it must be some sort of conversion issue but I can't see where.
Is it a conversion issue? Just bad sql? something else? Stats: I'm using SQL 7.0 sp4, The cas_lbs field is datat type FLOAT if that makes any difference. I commented out the WITH (NOLOCK) in case that was it. but no luck there either.
I've already tried CASTing the .01, .001 etc as FLOAT.
Interestingly, If I specify a value like .001 in the WHERE clause, the .001 sum and count will be correct, but every column after them will return the same SUM and COUNT. (Of course, every one befor will too...)
-
Well, I don't know what the problem is, and I have only SQL2k ... just an idea, did you try to specify all the numbers (100, 10, 1, 0.1 ....) with 4 decimal places? I remember seeing somewhere, that 1 is not always the same as 1.0000
-
This have to do with how CASE works.
One thing is that a CASE can only return one datatype - which one if there are multiple datatypes within the same CASE expression, is determined by prescedence.
(if not explicitly casted)
CASE also only evaluates if the expression is true or false.
Now.... the column checked is a float, but for the values above 1 all seems to work, but for those less than one it seems not.. Why is this?
The thing is - the datatype used to calculate within each CASE is - int.
This is because you say - if true then return 1 else 0
These two are integers
..and they cause the case to convert the values to check and then return to an int also.
So, all the low results are rounded up - thus they evaluate to the same sums and counts...
One way to fix this seems to be to say
WHEN column < 0.01 THEN 0.01 ELSE 0. END
WHEN column < 0.001 THEN 0.001 ELSE 0. END
WHEN column 0 when the condition is true - it will also return 0.001 instead of a single 1, but how to count that is left as an excercise for the reader.
/Kenneth
-
Just to sum up myself on the excercise - in order to return all 1's for you to sum and count - do like this:
WHEN column < 0.1 THEN 0.1 * 10 ELSE 0. END
WHEN column < 0.01 THEN 0.01 * 100 ELSE 0. END
WHEN column < 0.001 THEN 0.001 * 1000 ELSE 0. END
WHEN column < 0.0001 THEN 0.0001 * 10000 ELSE 0. END
=;o)
/Kenneth
-
I think it is a bug (sorry feature
) in SQL7. It works OK in SQL2K.The only way I found to get it to work in SQL7 is to select the results (without SUM) into a temp table then select from that table using SUM on each field.
Far away is close at hand in the images of elsewhere.
Anon. -
You do understand that multiple CASE conditions could be true for the same value and that a single value could be added to every total?
For example, if cas_lbs = .00005 then all the CASE statements would be true and this entry would be added to all the totals and counters.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
Thanks to all of you for your responses. Here's what I've found out based on them:
1) It appears to be a bug of some sort or at least such a subtle point in type conversion as to be indistinguishable from a bug
.2) It can be bypassed by specifying lots of precision in the case statement (As suggested by Vladden...1 is not the same as 1.00000). I also found that specifying the field type as Decimal (18,10) was more reliable than FLOAT.
3) Here's how I fixed it:
SELECT cas_no , SUM(CASE WHEN cas_lbs < 100.0000 THEN cas_lbs ELSE 0 END) HundredSum , SUM(CASE WHEN cas_lbs < 100.0000 THEN 1 ELSE 0 END) HundredCount , SUM(CASE WHEN cas_lbs < 10.0000 THEN cas_lbs ELSE 0 END) TenSum , SUM(CASE WHEN cas_lbs < 10.0000 THEN 1 ELSE 0 END) TenCount , SUM(CASE WHEN cas_lbs < 1.0000 THEN cas_lbs ELSE 0 END) OneSum , SUM(CASE WHEN cas_lbs < 1.0000 THEN 1 ELSE 0 END) OneCount , SUM(CASE WHEN cas_lbs < .1000 THEN cas_lbs ELSE 0 END) TenthSum , SUM(CASE WHEN cas_lbs < .1000 THEN 1 ELSE 0 END) TenthCount --Everything above this point returns correct results --NOW Everything below this point returns correct results too!!! , SUM(CASE WHEN cas_lbs < .0100 THEN cas_lbs ELSE 0 END) HundredthSum , SUM(CASE WHEN cas_lbs < .0100 THEN 1 ELSE 0 END) HundredthCount , SUM(CASE WHEN cas_lbs < .0010 THEN cas_lbs ELSE 0 END) ThousandthSum , SUM(CASE WHEN cas_lbs < .0010 THEN 1 ELSE 0 END) ThousandthCount , SUM(CASE WHEN cas_lbs < .0001 THEN cas_lbs ELSE 0 END) TenThousandthSum , SUM(CASE WHEN cas_lbs < .0001 THEN 1 ELSE 0 END) TenThousandthCount FROM #t --WITH (NOLOCK) WHERE cas_lbs < 100 --AND cas_no IN (50000) GROUP BY cas_no ORDER BY CHAR(LEN(cas_no) + 60) + CAST(cas_no AS varchar(12))
Thanks for all of your help!
Oh yeah, to Scott Pletcher, thanks for pointing that out...It is what I am going for in this case, but could be tricky for someone else reviewing the code.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply