Query help -- 1

[link9]

Ok, so here's a database table with one field in it -- the field contains numbers that range from 1 - 10, and some of the fields don't have entries at all (Ok, the table has more than one field, but let's keep it simple for now) --

So, what I would like to do is to be able to, with one SELECT statement, get a percentage of the fields that contain an 8, 9, or a 10, ignoring the blank fields...

i.e. if there are 12 fields, and two of them are blank (base just went to 10 from 12), 8 of them have an 8, 9, or a 10, and 2 of them have 1's, then the resulting value would be .8 -- 8 / 10

See what I'm getting at?

I currently use a more complicated method to get at this number -- with coding -- but would like to get it to work with SQL, so it would execute faster...

Thanks for any input!
Paul Prewett

 

[THoey]

Try this:

SELECT COUNT(*) AS CNT, 
Sum(IIf(Field1 >= 8 AND Field1 <= 10, 1, 0))/ CNT AS InRange 
FROM MyTable

Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

Woops, that doesn't ignore blanks... Let me think. I'll be back... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

Here you go...

SELECT COUNT(*) AS CNT, 
Sum(IIf(Field1 >= 8 AND Field1 <= 10, 1, 0))/ CNT AS InRange 
FROM MyTable
WHERE NOT ISNULL(Field1 )

Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[link9]

Great -- now please explain two things to me...

what is the , 1, 0 <--- telling the compiler here --

And if I wanted to add more WHERE criteria on the end, can I just say something like this:

WHERE (NOT ISNULL(Field1)) AND ... blahblahblah

just like other WHERE clauses...

and thanks by the way... you just made my applications run much much faster.

Paul Prewett

 

[link9]

But the query doesn't work in SQL Server... :-(

I know I posted in Access, but there are good folks over here, and I figured the thing would just port right over... I've posted over in SQL Server, but if anyone here knows how to make the same thing work in SQL Server, that'd be fantastic...

And thanks again. If nothing else, it got me thinking in the right vein on how to solve this.

paul