T-SQL Multiple Counts

I need to retrieve two counts from a table.  One to show the total number of entries for a position, and the other to show the total number of failures per position.  

I have two queries that provide the data, and I have been able to get it with a UNION, but not in the same row.
SELECT Position, COUNT(Value) AS VALUE, NULL AS Failures
 FROM tbl_VisionData
WHERE   '3/11/2009' <=  [TimeStamp] AND '4/17/2009' >=  [TimeStamp] AND (Value =2)  AND (Position <> 99)
GROUP BY Position
UNION
 
SELECT     Position, NULL, COUNT(Value) AS Failures
FROM         tbl_VisionData
WHERE     (TimeStamp > '3/11/2009') AND (TimeStamp < '4/17/2009') AND (Value < 3)
GROUP BY Position

Open in new window

meverts_daneAsked:
Who is Participating?
 
8080_DiverConnect With a Mentor Commented:
Instead of a UNION, use a FULL OUTER JOIN.  That will select all records from both subselects whether they have matches in the other one or not.  THat should get you a row for every position.  Thus, the COALESCE on each of the columns.
SELECT COALESCE(Y.Position, Z.Position), 
       Coalesce(Value, 0) AS Value, 
       COALESCE(Failures, 0) AS Failures
FROM
(
SELECT Position, COUNT(Value) AS VALUE, NULL AS Failures
 FROM tbl_VisionData
WHERE   '3/11/2009' <=  [TimeStamp] AND '4/17/2009' >=  [TimeStamp] AND (Value =2)  AND (Position <> 99)
GROUP BY Position
) Y
FULL OUTER JOIN   
( 
SELECT     Position, NULL, COUNT(Value) AS Failures
FROM         tbl_VisionData
WHERE     (TimeStamp > '3/11/2009') AND (TimeStamp < '4/17/2009') AND (Value < 3)
GROUP BY Position
) Z
ORDER BY Position

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this will work better:
SELECT Position
, SUM( CASE WHEN Value =2  AND Position <> 99 THEN 1 ELSE 0 END) AS VALUE
, SUM( CASE Value < 3 THEN 1 ELSE 0 END) AS failures
 FROM tbl_VisionData
WHERE '3/11/2009' <=  [TimeStamp] 
  AND '4/17/2009' >=  [TimeStamp] 
GROUP BY Position

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.