MSSQL Group until type is changed

Question

I currently have this table:

╔════╦══════════╦════════════╗
║ ID ║ PartType ║ PartStatus ║
╠════╬══════════╬════════════╣
║  1 ║ A        ║ OK         ║
║  2 ║ A        ║ BAD        ║
║  3 ║ A        ║ OK         ║
║  4 ║ A        ║ OK         ║
║  5 ║ B        ║ OK         ║
║  6 ║ B        ║ BAD        ║
║  7 ║ A        ║ OK         ║
╚════╩══════════╩════════════╝

I want to be able to group them by PartType UNTIL it changes. So it should output like this:

╔══════════╦══════════╗
║ PartType ║ Quantity ║
╠══════════╬══════════╣
║ A        ║        4 ║
║ B        ║        2 ║
║ A        ║        1 ║
╚══════════╩══════════╝

Answer 1

If you are using SQL Server 2012 or higher then another approach worth mentioning is to make use of the windowing functions available in 2012.

You can use the LAG function to detect when a state change has occurred within your dataset and you can use the SUM OVER clause to generate a grouping id for your data. The following example demonstrates how this can be done.

    DECLARE @parts TABLE
    (
        ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
        PartType nvarchar(1) NOT NULL,
        PartStatus nvarchar(50) NOT NULL
    )

    INSERT INTO @parts (PartType,PartStatus)
    VALUES 
    (N'A',N'OK'),
    (N'A',N'BAD'),
    (N'A',N'OK'),
    (N'A',N'OK'),
    (N'B',N'OK'),
    (N'B',N'BAD'),
    (N'A',N'OK');


    WITH CTE_PartTypeWithStateChange
    AS
    (
        SELECT   ID
                ,PartType
                ,PartStatus
                ,(
                    CASE
                        WHEN (LAG(PartType, 1, '') OVER (ORDER BY ID) <> PartType) THEN  1
                        ELSE 0
                    END
                    ) HasStateChanged
        FROM  @parts 
    )
    ,
    CTE_PartTypeWithGroupID
    AS
    (
        SELECT   ID
                ,PartType
                ,PartStatus
                ,SUM(HasStateChanged) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) AS GroupID
        FROM    CTE_PartTypeWithStateChange
    )
    SELECT   MAX(PartType) AS PartType
            ,COUNT(PartType) AS Quantity
    FROM     CTE_PartTypeWithGroupID
    GROUP BY GroupID

While it is a bit more code this approach does give you the benefit of reducing the number of reads on your source table since you are not performing any self joins. This approach also reduces the number of sorts that the query has to perform which should improve performance on larger data sets.