SQL Syntax help please -- 1

[link9]

The following query executes perfectly in Access -- but blows up in SQL Server on the same data:

SELECT COUNT(*) as CNT,
Sum(IIf(currentData.Q1A >= 8 AND currentData.Q1A <= 10, 1, 0))/ CNT AS Top3Box
FROM currentData
WHERE NOT ISNULL(Q1A);

Tells me I have a syntax error near the '>' on line 2.

Can anyone shed some light on it for me?

Thanks,
Paul Prewett

 

[Cagliostro]

Maybe VBA support IIF but it is not supported by SQL Server. John Fill

 

[link9]

Do you have a suggestion on how I would achieve the same thing in SQL Server??

I just want it to select the field(s) if
(1) the field is not Null
(2) the value is >= 8 AND <= 10

Thanks --
Paul Prewett

 

[Cagliostro]

select whatDoYouWant from source
where currentData beetween 8 and 10.
It will select all not null parameter between specified values.
If you want to select not null just put:
where youValue is not null
John Fill

 

[link9]

Yes, but the problem is that I need to divide the result of the sum operation by the number of total records that aren't null (but including the ones that aren't between 8 and 10) -- see my problem??

Here's what I came up with, but it's not right yet --

SELECT (SUM(currentData.Q1A) / COUNT(currentData.Q1A)) AS Top3Box
FROM currentData
WHERE ((currentData.Q1A BETWEEN 8 AND 10) AND currentData.Q1A IS NOT NULL);

See, so I'm just dividing the sum of the records that are between 8 and 10 --

Here's a little more detail about my problem... I would really appreciate a hand...

Say I have one field, and that field has numbers between one and ten. The measure that I want to be able to extract from the field is the percentage of those answers that are not null and between 8 and 10 --

If I had twelve rows, and two of them were null, and 8 of them were 8, 9, or 10, the answer that I need is .8 -- which is 8 / 10 <-- note the two nulls trashed for this calculation

Maybe I'm trying to make this too complicated, but as it is, I achieve this through code which executes much slower than a single SQL SELECT statement would. I just can't seem to think of a way to do it.

Thanks for any extra input you might have.
Paul Prewett

 

[Cagliostro]

I think you want:
(select SUM(currentData.Q1A) FROM currentData
WHERE (currentData.Q1A BETWEEN 8 AND 10))/
(SELECT COUNT(currentData.Q1A) from FROM currentData where currentData.Q1A IS NOT NULL)
or some thing like it.
John Fill

 

[link9]

Ok, here's where I've gotten --

It was throwing syntax at me on your '/', so I added a SELECT, threw the whole thing in there, and added an alias to the end to name the column output --

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

Problem is that I get a 0 as output each time. If I run the two subqueries separately, I get 698 and 1025, respectively, so I think we're really close on this one.

I think it's truncating itself to an integer value for some reason. How would I get around this?

Thanks again --
paul

 

[link9]

This works:

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

Thanks for the help. It got me on the right track.


paul