Repair orders query in SQL Server with subselects I was wondering if you guys know how to reduce the amount of subselects in this SQL Server Query? I think I'm using bad practices in this:

WITH TotalizedRepairOrders AS 
(SELECT SalesOrder,Items,DepartmentName, LastCompletedActivity,NextActivity,Plant,ParallelNextActivity  FROM OrdersSynthesis)

SELECT  Plant,
        DepartmentName,
        BlindsInQuestioning =( SELECT ISNULL(SUM(Items),0) FROM TotalizedRepairOrders WHERE NextActivity = 'NEW ORDER REVIEW' AND DepartmentName  = RO.DepartmentName),
        BlindsReadyToCross =( SELECT ISNULL( SUM(Items),0)  FROM TotalizedRepairOrders WHERE (LastCompletedActivity = 'LBM INVOICE' OR NextActivity  = 'LBM INVOICE' OR NextActivity ='PLANT ARRIVAL')  AND  DepartmentName  = RO.DepartmentName),
        BlindsPendingForShipmentCreation = ( SELECT  ISNULL(SUM(Items),0)  FROM TotalizedRepairOrders WHERE ( NextActivity  = 'RETURN SHIPMENT CREATION')  AND  DepartmentName  = RO.DepartmentName),
        PendingForInstructions = ( SELECT  ISNULL(SUM(Items),0)  FROM TotalizedRepairOrders WHERE ( ParallelNextActivity = 'INSTRUCTIONS' )  AND  DepartmentName  = RO.DepartmentName),
        PendingToBeRepaired = ( SELECT ISNULL( SUM(Items),0)  FROM TotalizedRepairOrders WHERE ( NextActivity = 'PRODUCTION' )  AND  DepartmentName  = RO.DepartmentName),
        PendingForQualityControl = ( SELECT  ISNULL(SUM(Items),0)  FROM TotalizedRepairOrders WHERE ( NextActivity = 'INSPECTION')  AND  DepartmentName  = RO.DepartmentName),
        PendingToBePacked = ( SELECT  ISNULL(SUM(Items),0)  FROM TotalizedRepairOrders WHERE ( NextActivity = 'PACKING LABEL')  AND  DepartmentName  = RO.DepartmentName)


FROM TotalizedRepairOrders RO GROUP BY  Plant ,DepartmentName
Here is a simpler method of your query, which doesn't use a CTE and a bunch of sub-selects.

SELECT
    Plant
    DepartmentName,
    SUM(CASE WHEN NextActivity = 'NEW ORDER REVIEW' THEN ISNULL(Items,0) ELSE 0 END) AS BLindsInQuestion,
    SUM(CASE WHEN LastCompletedActivity = 'LBM INVOICE' OR NextActivity  = 'LBM INVOICE' OR NextActivity ='PLANT ARRIVAL' THEN ISNULL(Items,0) ELSE 0 END) as BlindsReadyToCross
    /*
    etc
    */
FROM 
    TotalizedRepairOrders
GROUP BY  Plant ,DepartmentName