title: How to design T-SQL query to calculate sum in one pass?
body: <p>I am trying to develop a T-SQL query which will do the following:</p>
  
  <pre><code>ROUND(100 * A / B, 1)
  </code></pre>
  
  <p>Simple in concept, but it's tricky because of possible B=0 denominator and also because of A and B variables.  What I expect is a percent value like 93.2 (given in this format without %).  Or even 932 would be acceptable since I could convert it later.</p>
  
  <p>But instead, I'm currently getting 151, which is the number of records.  </p>
  
  <pre><code>A = CASE WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 1 ELSE 0 END
  B = CASE WHEN [Date_Completed] IS NOT NULL THEN 1 ELSE 0 END
  </code></pre>
  
  <p>My current logic only divides A/B if B is not equal to zero.  Can you please help me fix this?  p.s. all fields above are from the same table A.</p>
  
  <p>I tried:</p>
  
  <pre><code>SELECT CASE WHEN t.VarB&lt;&gt;0 THEN ROUND(100 * t.VarA / t.VarB, 1) 
  ELSE 0 /* or whatever you'd want to return in this case */ 
  END 
  FROM (SELECT CASE WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 1 
  ELSE 0 
  END AS VarA, 
  CASE WHEN [Date_Completed] IS NOT NULL THEN 1 
  ELSE 0 
  END AS VarB 
  FROM EXCEL.Batch_Records A) t
  </code></pre>
  
  <p>But I got 33000 rows returned instead of just one, where each row = 100 or 0.  </p>
  
  <p>Good idea, Conrad! I tested your solution and it works if I just want that one value.  But what I didn't tell you was that there are additional values I need returned from same query.  When I tried adding in the other value calculations, I got syntax errors.  So here is my current query.  How should htis be rewritten please?</p>
  
  <pre><code>select 
  SUM(CASE WHEN A.DATE_RECEIVED IS NOT NULL THEN 1 ELSE 0 END) AS NUM_RECEIVED,
  SUM(CASE WHEN [Date_Completed] IS NOT NULL THEN 1 ELSE 0 END) AS NUM_COMPLETE_OF_OPENED,
  SUM(CASE WHEN A.DATE_COMPLETED IS NOT NULL THEN 1 ELSE 0 END) AS NUM_COMPLETED_IN_MONTH,
  SUM(CASE WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 1 ELSE 0 END) AS NUM_WITHOUT_ERROR,
  
  round(100 * a/b , 1) 
  from 
  (select 
      sum(CASE  
          WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN  
              1.0  
          ELSE 0.0 END) A, 
      sum(CASE WHEN [Date_Completed] IS NOT NULL THEN 
  
  1.0 ELSE 0.0 END) B 
  
  FROM EXCEL.Batch_Records a 
  LEFT JOIN EXCEL.QC_CODES d ON a.Part_Number = d.CODE_ID    
  WHERE (a.[Group] = @GROUP or @GROUP = '' OR @GROUP IS NULL) AND A.Date_Received &gt;= @STARTDATE AND A.Date_Received &lt;= @ENDDATE
  </code></pre>
  
  <p>Conrad correctly advised me that #TEMP1 was an empty table.  But now I populated it and successfully designed this query with his help:</p>
  
  <pre><code>SET @STARTDATE = '1/1/11'
  SET @ENDDATE = '1/31/11'
  SET @GROUP = 'INTERMEDIATES_FISH'
  --SET @TABLE_TITLE = 'BATCH RECORD SUCCESS RATE'
  --SET @DEPT = 'QC'     
  
  IF EXISTS(SELECT * FROM TEMPDB.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#TEMP1%')
  DROP TABLE #TEMP1
  
  --CREATE TABLE #TEMP1 (     MFG int ,      MFG2 int ,     QC int,      QC2 INT ,      [Group] NVARCHAR(MAX),     [Date_Completed] datetime,     Date_Received datetime)
  SELECT
  MFG, MFG2, QC, QC2, [GROUP], [DATE_COMPLETED], [DATE_RECEIVED]
  INTO #TEMP1
  FROM EXCEL.Batch_Records a 
  WHERE (a.[Group] = @GROUP or @GROUP = '' OR @GROUP IS NULL) AND A.Date_Received &gt;= @STARTDATE AND A.Date_Received &lt;= @ENDDATE
  
  ------------------------------------------  
  ;WITH CTE AS 
  ( 
  SELECT 
  CASE 
  WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 
  1.0 
  ELSE 0.0 END A, 
  CASE WHEN [Date_Completed] IS NOT NULL THEN 1.0 ELSE 0.0 END B, 
  CASE WHEN A.Date_Received IS NOT NULL THEN 1 ELSE 0 END NUM_RECEIVED, 
  CASE WHEN [Date_Completed] IS NOT NULL THEN 1 ELSE 0 END NUM_COMPLETE_OF_OPENED, 
  CASE WHEN A.DATE_COMPLETED IS NOT NULL THEN 1 ELSE 0 END NUM_COMPLETED_IN_MONTH, 
  CASE WHEN A.MFG IS NULL AND A.MFG2 IS NULL AND A.QC IS NULL AND A.QC2 IS NULL THEN 1 ELSE 0 END AS NUM_WITHOUT_ERROR 
  FROM 
  #TEMP1 a 
  --WHERE (a.[Group] = @GROUP or @GROUP = '' OR @GROUP IS NULL) AND A.Date_Received &gt;= @STARTDATE AND A.Date_Received &lt;= @ENDDATE
  ) 
  
  select 
  round(100 * SUM(A)/SUM(b) , 1) , 
  SUM(NUM_RECEIVED) NUM_RECEIVED, 
  SUM(NUM_COMPLETE_OF_OPENED) NUM_COMPLETE_OF_OPENED, 
  SUM(NUM_COMPLETED_IN_MONTH) NUM_COMPLETED_IN_MONTH, 
  SUM(NUM_WITHOUT_ERROR) NUM_WITHOUT_ERROR 
  
  
  FROM CTE 
  </code></pre>