Post reply

String Pattern Grouping

  • September 9, 2008 at 6:12 pm

    #72918

    I am trying to count rows which have a column that matches a specific pattern. I have a "Status" column, and I want to count all of the "ERR%", "FAIL%", "PASS%", and "Other" values.

    I have been looking at the string processing functions, and don't see anything that might help with this.

    Any ideas?

    Thanks,

    Justin

    My input data would look like:

    DECLARE @raw TABLE (TestListID INT, Feature VARCHAR(256), RunStatus VARCHAR(64))

    INSERT @raw

    SELECT 1, 'Sample', 'FAIL_HW' UNION ALL

    SELECT 2, 'trial', 'PASS' UNION ALL

    SELECT 3, 'Stride', 'Error_SW' UNION ALL

    SELECT 4, 'Regression', 'PASS_SW' UNION ALL

    SELECT 5, 'Regression', 'PASS_HW' UNION ALL

    SELECT 6, 'Setup', 'Waived' UNION ALL

    SELECT 7, 'Setup', 'Defered' UNION ALL

    SELECT 8, 'Setup', 'Error_HW' UNION ALL

    SELECT 9, 'Regression', 'Error' UNION ALL

    SELECT 10, 'Setup', 'Error' UNION ALL

    SELECT 11, 'Unit', 'Ready' UNION ALL

    SELECT 12, 'Unit', 'Ready' UNION ALL

    SELECT 13, 'Unit', 'Ready' UNION ALL

    SELECT 14, 'Unit', 'Ready'

    And the output should look like:

    Feature Status Count

    ------- ------ -----

    Regression Err* 1

    Regression Pass* 2

    Sample Fail* 1

    Setup Err* 2

    Setup Other 2

    Stride Err* 1

    trial Pass* 1

    Unit Other 4

  • September 9, 2008 at 9:09 pm

    #869372

    You would want to do a GROUP BY and COUNT to aggregate up the rows.

    select fieldA, fieldB, count(fieldb)

    from table

    group by fieldA, fieldB

  • September 10, 2008 at 9:52 am

    #869775

    That will count all of the like items in fieldB, but I want to count all of the items in fieldB which match a specific pattern. For example, "Pass_HW" and "Pass_SW" should be considered the same and counted the same.

  • September 10, 2008 at 9:32 pm

    #870101

    SELECT Feature,

    Status=CASE WHEN patindex('Err%',RunStatus)>0 THEN 'Err*'

    WHEN patindex('Fail%',RunStatus)>0 THEN 'Fail*'

    WHEN patindex('Pass%',RunStatus)>0 THEN 'Pass*'

    ELSE 'Other'

    END,

    [Count]=count(1)

    FROM @raw

    GROUP BY Feature,CASE WHEN patindex('Err%',RunStatus)>0 THEN 'Err*'

    WHEN patindex('Fail%',RunStatus)>0 THEN 'Fail*'

    WHEN patindex('Pass%',RunStatus)>0 THEN 'Pass*'

    ELSE 'Other'

    END

    ORDER BY Feature

  • September 12, 2008 at 10:49 am

    #871152

    That was perfect, thanks for the help!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply