Need Non Distinct Union ( how ?)

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

SQL Server 2000 Forums

SQL Server Development (2000)

Need Non Distinct Union ( how ?)

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-01-31 : 23:54:21

I have data that represents test data attempts.
Around that test data I am making average and std dev calcs.

Data looks like this ( I'd like to represent it as stacked bar but please try to follow

test unit 1 ( P )
test unit 2 ( F ) ( p ) ---> passed on 2nd attempt.. could be more
test unit 3 ( P )
test unit 4 ( F ) ( p ) ---> passed on 2nd attempt

Ideally I'd end up with a column of 4 rows
0 ( no failures )
1 ( represents the failure count )
0
1

These are the two queries - but if I Union them, only distinct rows are selected ... so I can take the average or the stdev..

SELECT COUNT(*) - 1 AS FAILED
FROM dbo.Test
WHERE (PassStatus = 'PASS') AND (PROC_FKey = 179) AND (TestTime >= '1/23/2006') AND (TestTime <= '1/31/2006') AND (AttemptNumber = 1)
GROUP BY CSB_FKey

[ gets me a column of 2 0's ] OK

SELECT COUNT(*) AS FAILED
FROM dbo.Test AS TestFlightProcedureDynamic_1
WHERE (PassStatus <> 'PASS') AND (PROC_FKey = 179) AND (TestTime >= '1/23/2006') AND (TestTime <= '1/31/2006')
GROUP BY CSB_FKey

[ gets me a column of 2 1's ] OK

But How do I push them together and so I can wrap them with

SELECT AVG(FAILED) AS inTotal
FROM ( the columnar query result of above )
AS MY_STATISTIC

Thanks ! Andrew

andrewcw

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-01-31 : 23:55:41

Sorry I bumped the submit ..topic should be how to union that's not distinct

andrewcw

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-01 : 01:43:51

can you post your table structure and same sample data as well as expected result ?

----------------------------------
'KH'

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-02-01 : 02:47:42

Instead of using "UNION" have you tried using "UNION ALL"

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-02-01 : 10:50:59

PASSSTATUS PROC_FKEY ATTEMPTNUMBER CSB_FKEY
FAIL 176 1 A
PASS 176 2 A
FAIL 176 1 B
PASS 176 2 B
FAIL 176 1 C
PASS 176 2 C
PASS 176 1 D
PASS 176 1 E
PASS 177 1 F

For my data
( the first query part gave me this )
0
0

( the 2nd query part gives me this )
1
1
1

The UNION ALL does gets me EXACTLY WHAT I NEEDED. THANKS !!!
0
0
1
1
1

When rapped with a STDEV - I get the answer I expect ( 0.547722 ), BUT with AVG its 0....???

SELECT STDEV(FAILED) AS inTotal
FROM (SELECT COUNT(*) AS FAILED
FROM TEST
WHERE (PASSSTATUS <> 'PASS') AND (PROC_FKEY = 176)
GROUP BY CSB_FKEY
UNION ALL
SELECT COUNT(*) - 1 AS FAILED
FROM TEST AS TEST_1
WHERE (PASSSTATUS = 'PASS') AND (PROC_FKEY = 176) AND (ATTEMPTNUMBER = 1)
GROUP BY CSB_FKEY) AS MYSTAT

ANY IDEAS ????

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources