SQL Server：带有内部 SELECT 和子 SELECT 的 SUM()。每次都出错

问题重述

我在 SQL 语句中有以下列定义，它有自己的GROUP BY......

    SUM((SELECT a.CONT_TOT
     FROM  (SELECT   gl2.VisitID, gl2.MessageID, gl2.BillOfLading, COUNT(gl2.ContainerID) AS CONT_TOT
            FROM     dbo.tblEDIGoodsLines AS gl2
            WHERE    gl2.VisitID = gl.VisitID AND gl2.MessageID = gl.MessageID AND gl2.BillOfLading = gl.BillOfLading
            GROUP BY gl2.VisitID, gl2.MessageID, gl2.BillOfLading) AS a)) as TotalContainers,    

...etc

我不断收到这个错误。

无法对包含聚合或子查询的表达式执行聚合函数。

我正在尝试获取外部/更大的 SELECT 中的总行数，以及 TOTUCONT 中 UNIQUE 容器的总数。

我究竟做错了什么？

这是更大的 SQL 查询，以说明我对 SUM() 等聚合函数中的 GROUP BY 和子查询的观点：

SELECT
    gl.MessageID,
    gl.BillOfLading,
    gl.[Description],
    CASE WHEN e.PortID = 9 THEN 'Export'
      WHEN e.PortID = 11 THEN 'Import'
      ELSE 'ERROR'
    END AS Direction,
    CASE WHEN ctypes.ID IS NOT NULL
         THEN ctypes.ContainerSizeType 
         ELSE 'OTH'
    END AS CSizeType,
    ctypes.Length_ft + 'ft ' + ctypes.Height_ft + 'ft - ' + ctypes.Characteristics + ' (' + COALESCE(ctypes.Codes1995, ctypes.Codes1984) + ')' AS ContainerType,
    COUNT(gl.ContainerID) AS TOTCONT,
    SUM(a.CTOTAL) AS TOTUCONT
FROM tblEDIGoodsLines AS gl 
    INNER JOIN tblEDIEquipmentLines AS el 
        ON el.MessageID = gl.MessageID AND
           el.ContainerID = gl.ContainerID
    INNER JOIN tblEDI AS e
        ON CHARINDEX(e.MessageID, gl.MessageID) > 0 AND
           e.VisitID = gl.VisitID AND
           CHARINDEX('EXCEL', e.MessageRelease) = 0 AND
           e.Status = 1
    LEFT JOIN tblContainerTypesISO6346 AS ctypes 
        ON ctypes.Codes1984 = el.SizeAndType OR 
           ctypes.Codes1995 = el.SizeAndType
    LEFT JOIN (SELECT gl2.MessageID, gl2.VisitID, gl2.BillOfLading, gl2.description, COUNT(DISTINCT gl2.ContainerID) AS CTOTAL
              FROM tblEDIGoodsLines AS gl2 
              WHERE gl2.MessageID = gl.MessageID 
                AND gl2.VisitID = gl.VisitID 
                and gl2.BillOfLading = gl.billoflading 
                and gl2.description = gl.description
              GROUP BY gl2.MessageID, gl2.VisitID, gl2.BillOfLading, gl2.description) AS a
        ON a.MessageID = gl.MessageID AND a.VisitID = gl.VisitID AND a.BillOfLading = gl.billoflading AND a.description = gl.description
WHERE gl.Status = 1
  AND gl.VisitID = 22987
GROUP BY
    gl.MessageID,
    gl.BillOfLading,
    gl.[Description],
    CASE WHEN e.PortID = 9 THEN 'Export'
         WHEN e.PortID = 11 THEN 'Import'
         ELSE 'ERROR'
    END,
    CASE WHEN ctypes.ID IS NOT NULL
         THEN ctypes.ContainerSizeType 
         ELSE 'OTH'
    END,
    ctypes.Length_ft + 'ft ' + ctypes.Height_ft + 'ft - ' + ctypes.Characteristics + ' (' + COALESCE(ctypes.Codes1995, ctypes.Codes1984) + ')'

以上无论如何都不起作用，因为我试图通过将“列” SELECT 分离到它自己的 JOIN 查询中来解决这个问题，但现在我明白了：

无法绑定多部分标识符“gl.MessageID”。

所以这意味着，LEFT JOIN (SELECT...) 是无效的？

再次感谢

更新 2

下面是一个数据样本来进一步解释：

所以你可以看到我想要的结果，TOTCONT 加起来为“4”。这很简单 - 只需计算行数，但 TOTUCONT 只计算容器 ID 一次。