CODEWITHSUNDEEP
sql
The Dude
The Dude

Reputation: 340

SQL Count: How to get a count on identical values in a table

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.

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 %.

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)

My database tables are structured like this (only including relevant ones):

Table: Measurements

Columns: Status (Pass/Fail), SMP ID, Name 

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

Upvotes: 0

Views: 132

Answers (1)

Paul Chernoch
Paul Chernoch

Reputation: 5553

It seems that what you need to do is use a GROUP BY clause, and GROUP BY the [Measurement Name]. This will give you subtotals, and you won't need to know what the list of [Measurement Names] is when writing your query.

select 
      [Measurement Name]
    , [SMP ID]
    , [Fail Count] = SUM([Fail])
    , [Not Fail Count] = SUM([Not Fail])
    , [Fail %] = 100 * SUM([Fail]) / SUM(1)
from [Measurements]
cross apply (SELECT [Fail] = case when [Status] = 'Fail' then 1 else 0 end) [X_Fail]
cross apply (SELECT [Not Fail] = case when [Status] <> 'Fail' then 1 else 0 end) [X_Not Fail]
group by [Measurement Name], [SMP ID]

I am not sure about putting the [SMP ID] in the select and GROUP BY, because I do not know your model and data relationships. You might want to drop it.

Upvotes: 2

Related Questions