title: SQL Count: How to get a count on identical values in a table
body: <p>I am trying to get a query that returns the fail percentage ("fail %" from now on) for each [Measurement Name].  Right now, I have queries below that can return the fail % for a specific value (entered as @MEASUREMENT) for either specific SMP (sample) or a wildcard SMP.  </p>
  
  <p>I am having trouble, however, writing a query that would return the fail % for all measurement values where the fail % is unique to that value. The problem is that I can only get the COUNT function to count all the fails in the measurement table if @MEASUREMENT is a wildcard, so every fail will have the same Fail %. </p>
  
  <p>Is there a way to solve this problem other than using a table that links a table where the measurement name is the primary key to to my other tables? (I do not think this would be optimal in my case since there would be many different measurement names)</p>
  
  <p>My database tables are structured like this (only including relevant ones):</p>
  
  <p>Table: Measurements</p>
  
  <p>Columns: Status (Pass/Fail), SMP ID, Name </p>
  
  <pre><code>DECLARE @SMP varchar(50)
  DECLARE @MEASUREMENT varchar(50)
  
  SET @SMP = ''
  SET @MEASUREMENT = 'Value 1'
  
  IF(@SMP = '')
      SET @SMP = '%'
  
  IF(@SMP = '%')
      SELECT DISTINCT  
             [Measurements].[Measurement Name], 
              ((CONVERT(float, (SELECT COUNT(*) FROM dbo.[Measurements] 
                  WHERE [Status] = 'Fail' AND [Measurement Name] LIKE @MEASUREMENT AND [SMP ID] LIKE @SMP)) / 
              (CONVERT(float, (SELECT COUNT(*) FROM dbo.[Measurements]
                   WHERE [Measurement Name] = @MEASUREMENT AND [SMP ID] LIKE @SMP)))
             )) As [Fail %]
      FROM dbo.[Measurements]
          WHERE [Measurements].[SMP ID] LIKE @SMP
              AND [Measurements].[Measurement Name] LIKE @MEASUREMENT
  ELSE
      SELECT DISTINCT [Measurements].[SMP ID], 
             [Measurements].[Measurement Name], 
              ((CONVERT(float, (SELECT COUNT(*) FROM dbo.[Measurements] 
                  WHERE [Status] = 'Fail' AND [Measurement Name] LIKE @MEASUREMENT AND [SMP ID] LIKE @SMP)) / 
              (CONVERT(float, (SELECT COUNT(*) FROM dbo.[Measurements]
                   WHERE [Measurement Name] = @MEASUREMENT AND [SMP ID] = @SMP)))
              )) As [Fail %]
      FROM dbo.[Measurements]
          WHERE [Measurements].[SMP ID] LIKE @SMP
              AND [Measurements].[Measurement Name] LIKE @MEASUREMENT
  </code></pre>