obtains percentage from 2 fields with inner select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

obtains percentage from 2 fields with inner select

Author

Topic

Dmh188
Starting Member

37 Posts

Posted - 2010-10-22 : 09:46:13

Hey guys, Im having a lot of trouble trying to get this query to run correctly. Basically i am trying to get the percentage for oos items. The fields are active (T,F), itemno (item number), Qty_Stk(total qauntity in stock). Here is what i have but it is not working and i cant seem to figure out the problem. Any help would be greatly appreciated as am still new to sql.

SELECT count((itemno)select itemno from items where active=t) / count((itemno)select itemno from items where active= 't' and qty_stk= .0000)
* 100.0 as Percent FROM items GROUP BY itemno

Dmh188
Starting Member

37 Posts

Posted - 2010-10-22 : 09:49:07

Sorry not inner select, was working on something else while writing this!

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-10-22 : 11:10:29

Run these queries:



SELECT 5/4

SELECT 5e0/4e0

SELECT CAST(5 as real)/CAST(4 as real)

In the first, you get the result in the same data type as the inputs, so you'll need to cast the results of your counts.

As for the code you have posted, it shouldn't even work! You must have got a syntax error running that. Also, why are you dividing total active items by active items out of stock? That'll give you a percentage over 100%.


    
SELECT 
    Percentage=1-CAST(count(nullif(Qty_Stk,0)) as real)/    --use nullif as count only counts non null results. Assuming no null results exist for Qty_Stk, otherwise change
    CAST(count(1) as real)  
FROM Items WHERE active='t'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources