SELECT
  IF(A.ProductCategory = @aAdHocProductCategory, 1, 0) AS IsAdHoc,
  A.ProductCategory,
  COALESCE(S1.OptionDescription, '') AS ProductCategoryGroup,
  U1.FullName AS ProductManager,
  TrackingCategory,
  PC.ProductCount,
  CC.ConsultantCount,
  AC.AttendanceTypeCount,
  CS.ClientCount,
  A.BillTravelTime,
  A.InternalProduct,
  A.MustHaveParent,
  A.CoverAllowed
FROM
  taProductCategory AS A
    LEFT JOIN taUsers AS U1 ON (U1.UserID = A.ProductManagerID)
    LEFT JOIN taSystemOption AS S1 ON ((S1.OptionType = 'PRODUCT-CATEGORY-GROUP') AND (S1.OptionID = A.ProductCategoryGroupID))
    LEFT JOIN (

      SELECT
        PR.ProductCategory,
        COUNT(PR.ProductID) AS ProductCount
      FROM
        taProducts AS PR
      WHERE
        (PR.IsDormant = 0)
      GROUP BY
        PR.ProductCategory

    ) AS PC ON (PC.ProductCategory = A.ProductCategory)
    LEFT JOIN (

      SELECT
        X.ProductCategory,
        COUNT(X.ConsultantID) AS ConsultantCount
      FROM (
        SELECT DISTINCT
          PR.ProductCategory,
          BP.ConsultantID
        FROM
          taProducts AS PR
            LEFT JOIN taClientProducts AS CP ON (CP.ProductID = PR.ProductID)
            LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientProductID = CP.ClientProductID)
        WHERE
          (PR.IsDormant = 0) AND (CP.Status = 1) AND (BP.Status = 1)
        ) AS X
      GROUP BY
        X.ProductCategory

    ) AS CC ON (CC.ProductCategory = A.ProductCategory)
    LEFT JOIN (

      SELECT
        AT.ProductCategory,
        COUNT(*) AS AttendanceTypeCount
      FROM
        tbAppointmentTypes AS AT
      WHERE
        (AT.IsActive = 1)
      GROUP BY
        AT.ProductCategory

    ) AS AC ON (AC.ProductCategory = A.ProductCategory)
    LEFT JOIN (

      SELECT
        X.ProductCategory,
        COUNT(X.ClientID) AS ClientCount
      FROM (
        SELECT DISTINCT
          PR.ProductCategory,
          CP.ClientID
        FROM
          taProducts AS PR
            LEFT JOIN taClientProducts AS CP ON (CP.ProductID = PR.ProductID)
        WHERE
          (PR.IsDormant = 0) AND (CP.Status = 1)
      ) AS X
      GROUP BY
        X.ProductCategory

    ) AS CS ON (CS.ProductCategory = A.ProductCategory)
ORDER BY
  1 DESC, 2