[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 866
  • Last Modified:

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

0
meverts_dane
Asked:
meverts_dane
2 Solutions
 
8080_DiverCommented:
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]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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now