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.
| Author |
Topic |
|
andrewcw
Posting Yak Master
133 Posts |
 Posted - 2006-01-01 : 00:51:58
|
| I need to get the STDEV on the number of failures..across all CSB_Fkey'sProc_FKey CSB_FKey PASSSTATUS PROCECURENUMBER1234 YM27027111 FAIL1234 YM27027111 PASS1234 YM27127111 FAIL1234 YM27127111 FAIL 1234 YM27127111 PASS 1234 YM27227111 PASSUsing this : SELECT CSB_FKEY, COUNT(*) AS FAILEDFROM dbo.AWTEST1WHERE (PASSSTATUS <> 'PASS') AND (Proc_Fkey = 1234)GROUP BY CSB_FKEY CSBKey Failed YM27027111 1 YM27127111 2 But Now to get the STDEV I think I would wrap the the above query in something like :Select STDEV(FAILED) as inTotal from (SELECT CSB_FKEY, COUNT(*) AS FAILEDFROM dbo.AWTEST1WHERE (PASSSTATUS <> 'PASS') AND (Proc_Fkey = 1234)GROUP BY CSB_FKEY) But I get an ERROR with INCORRECT SYNTAX NEAR THE KEYWORD 'SET'. SO any clues if this can be done ?? Thanks !!!andrewcw |
|
|
Jothikannan
Starting Member
36 Posts |
Posted - 2006-01-01 : 07:13:42
|
| HI TRY THISSelect STDEV(FAILED) as inTotal from (SELECT CSB_FKEY, COUNT(*) AS FAILEDFROM dbo.AWTEST1WHERE (PASSSTATUS <> 'PASS') AND (Proc_Fkey = 1234)GROUP BY CSB_FKEY) AS MY_AS |
 |
|
|
andrewcw
Posting Yak Master
133 Posts |
Posted - 2006-01-01 : 13:46:06
|
| Thank you ! It works perfectly and I see better how to use the results of one query to feed a second ( nest ) !andrewcw |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-02 : 01:40:04
|
| You need to use Table alias as suggested. Look for Derived tables in Books On Line, SQL Server help fileMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Do I need temp table for query?