SQL Statement help --

[link9]

Hello all --

Here's the situation -- I need to pull a simple average from a table based on a few criteria, but I can't seem to come up with the correct syntax... I'm using SQL Server.

I have a field in a table that contains numbers from 1 to 10, and some that are NULL --

I need to be able to pull a decimal value from the table that represents the percentage of values that are between 8 and 10, but not including the NULL values in the base that I divide by.

example:
12 rows
2 -- NULLs
8 -- 8, 9, or 10's
2 -- 1 - 7's

Value desired would be .8 -- 8/10

See what I'm getting at?

Thanks for any input --
Paul Prewett

 

[link9]

A little update:

Per another member's response in the SQL Server forum, I've come this far:

SELECT
(SELECT COUNT(currentData.Q24) FROM currentData
WHERE (currentData.Q24 BETWEEN 8 AND 10)) /
(SELECT COUNT(currentData.Q24) FROM currentData WHERE currentData.Q24 IS NOT NULL)
AS top3Box

Where if I run the two subqueries by themselves, I come up with 698 and 1025, respectively -- and so this should yield an answer for me in the top3Box output of .68 -- but it doesn't -- it gives me 0

So I'm assuming that it's truncating itself to an integer --

Any ideas on how this might get fixed?

Thanks -
paul

 

[link9]

For anyone interested, here's the solution:

SELECT(
(SELECT CAST(COUNT(currentData.Q24) AS float) FROM currentData WHERE (currentData.Q24 BETWEEN 8 AND 10)) /
(SELECT CAST(COUNT(currentData.Q24) AS float) FROM currentData WHERE (currentData.Q24 IS NOT NULL))
)AS top3Box


paul

 

[MalcolmW]

Good work Paul - I'm glad to see you getting such quick responses on the forum ;-)
As this problem bit me once too, the logic behind what is happening is that if you use just integers in an expression, the result of the expression is also an integer.
The expression 8/10 will result in 0 (integer result).
However, the expression 8.000/10 will result in 0.800.
So, anything to that explicititly adds precision to one or more of the numbers in the expression will do the trick. Malcolm

 

[karluk]

That's very interesting. I tried a similar calculation in Oracle, and got the correct fraction without any special gyrations. I guess I'm glad that Oracle has this flexibility, but the purist in me says that it's better to have the result of integer arithmetic remain an integer.