SQL Server: SUM() with internal SELECT and sub SELECT. Error every time

Question

QUESTION REWORDED

I had the following column definition in an SQL statement with its own 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

And I continuously got this error.

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I am trying to get the TOTAL number of lines in the outside/bigger SELECT, and a total number of UNIQUE container counts in the TOTUCONT.

What am I doing wrong?

Here is the greater SQL query, to illustrate my point about GROUP BY and sub-queries in an aggregate function like SUM():

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) + ')'

The above does not work anyway, as I am trying to solve this by separating the "column" SELECT into it's own JOIN query, but now I get this:

The multi-part identifier "gl.MessageID" could not be bound.

So this implies, that the LEFT JOIN (SELECT...) is invalid?

Thanks again

UPDATE 2

Here is a data sample to explain further:

So you can see the outcome I want, TOTCONT to add up to '4'. That's easy - just count the number of rows, but TOTUCONT to count only the Container ID once.

Answer 1

Try refactoring the code to be more like this (as I commented earlier). It may also help with the other issues you are dealing with:

with cte_ctotal
as (
    select gl2.MessageID,
        gl2.VisitID,
        gl2.BillOfLading,
        gl2.description,
        COUNT(distinct gl2.ContainerID) as CTOTAL
    from tblEDIGoodsLines as gl2
    group by gl2.MessageID,
        gl2.VisitID,
        gl2.BillOfLading,
        gl2.description
    ),
cte_containers
as (
    select
    a.ContainerID,
    gl.MessageID,
    gl.VisitID,
    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,
    from tblEDIGoodsLines gl
    inner join tblEDIEquipmentLines el on el.MessageID = gl.MessageID
                                        and el.ContainerID = gl.ContainerID
    inner join tblEDI 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 ctypes on ctypes.Codes1984 = el.SizeAndType
                                              or ctypes.Codes1995 = el.SizeAndType
    where gl.status = 1
    and gl.VisitID = 22987
    )
select 
c.MessageID,
c.BillOfLading,
c.Description,
c.Direction,
c.CSizeType,
c.ContainerType,
COUNT(c.ContainerID) as TOTCONT,
SUM(COALESCE(ct.CTOTAL,0)) as TOTUCONT
from cte_containers c
left join cte_ctotal ct on ct.MessageID = c.MessageID
                       and ct.VisitID = c.VisitID
                       and ct.BillOfLading = c.billoflading
                       and ct.description = c.description
group by c.MessageID,
    c.BillOfLading,
    c.Description,
    c.Direction,
    c.CSizeType,
    c.ContainerType;

Answer 2

Change to this:

SUM(a.CONT_TOT) as TotalContainers
     FROM  (
       SELECT   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,

You may need to use parentheses at the end depending on the use of the code.
I removed additional columns from the SELECT list as they are not needed to get the SUM() (unless you need them for something else?).
Edit
Remove the WHERE clause from the last JOIN:

WHERE gl2.MessageID = gl.MessageID 
  AND gl2.VisitID = gl.VisitID 
  and gl2.BillOfLading = gl.billoflading 
  and gl2.description = gl.description

These conditions are applied in the ON clause.

Answer 3

I think you can simplify this to:

(SELECT COUNT(gl2.ContainerID)
 FROM dbo.tblEDIGoodsLines gl2
 WHERE gl2.VisitID = gl.VisitID AND
       gl2.MessageID = gl.MessageID AND
       gl2.BillOfLading = gl.BillOfLading
) as TotalContainers,    

Notes:

• The outer SUM() is unnecessary. You can do the aggregation in the subqueries.
• You do not need two levels of subqueries.
• The GROUP BY is unnecessary. An aggregation query with no GROUP BY always returns exactly one row, which is what you want for a scalar subquery.

Depending on the rest of your query, this still might not work in your full query. If this is the case, then you should ask a new question, with sample data, desired results, and (a simplified version of) the query that does not work.

Answer 4

Try this,

 (SELECT SUM(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,